Look Up Table and Circular Dependency Detected

Power BI
1

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

2

Here is a reply from Sam in the past regarding Circular Dependencies. When asking questions like this in the forum, it is imperative that you include a sample PBIX file so that we can better assist.

image
image820Ă—477 33.9 KB

Thanks
Enterprise%20DNA%20Expert%20-%20Small

3

VALUES can return a blank row, Distinct will not. Though would need to see the data for sure.

With that being said, this is the type of thing that really should be done in Power Query.

2 Likes
4

To the contributors of this post, thank you for all your inputs on this topic we are now tagging it as Solved. To help us learn more about your experience in the forum, please take a moment to answer this short forum survey. We appreciate all your help and suggestions. Thanks!