I am trying to derive look up tables from fact table by using VALUES function. But for some of them, when I create relationships, it says circular dependency detected. Here are some cases:
StateDim=VALUES(Consolidated[State]): no issues when created relationship
For Testing Purpose, I tried:
WokrItemDim=VALUES[(Consolidated[ID]): ID column is the primary ID of the fact table. Circular dependency detected
WorkItemDim-DISTINCT[(Consolidated[ID]): no issues.
PathValues=VALUES(Consolidated[Path]): circular dependency detected
PathDist=DISTINCT(Consolidated[Path]): circular dependency detected
Consolidated[Path] is a calculated column and every value is unique. I was planning to expand with additional columns but when I tried to create relationship with the fact table, It gives the “circular dependency detected”.
Q1. Why there is no issue when I use VALUES on State but on ID?
Q2. When we created calculated look up tables, which one is better? VALUES or DISTINCT?
Q3. Are we not supposed to create dim tables/create relationships based on/between calculated columns