Forum Discussion
Circular dependency on calculated column
Hi community,
I have an issue with a circular dependency on a calculated column.
I have created a first calculated column on my factFundReport table, called 'New TNAV' that works perfectly fine:
When I create a second calculated column on the same table I get the circular dependency error:
I don't understand why the error refers to factFundReport[New TNAV] calculated column (above) as there is no obvious reference. When I remove the LOOKUPVALUE, the error disappears:
Any useful pointers in the right direction or tool suggestions are much appreciated.
Many thanks.
Hi v-xiandat-msft,
Thank you for your reply and taking the time to explain the nuances of calculated columns - I still have a lot to learn about Power BI and DAX!
Your comment got me thinking about the many-to-one relationship in my factFundReport to factFund tables and, seeing as I only want to find one factFund entry I used the RELATED instead of LOOKUPVALUE, which solved my circular dependency!New Subfunds = VAR SubFundCount = RELATED(factFund[Sub Fund Count]) // LOOKUPVALUE // ( // factFund[Sub Fund Count], // factFund[FundKey], factFundReport[FundKey] // ) RETURN IF(factFundReport[Is New],SubFundCount, 0)
Hoping this can help others stuck on circular dependencies.
2 Replies
Hi bexbissell ,
Let's break it down:
- When creating a computed column, the DAX engine computes the column row by row. The context of each row is considered and dependencies are tracked.
- In the second column, you use the LOOKUPVALUE function, which involves a contextual transformation. This conversion introduces a filter context for each row, including references to other columns.
- This is where the circular dependencies sneak in: the second column depends on the first column (New TNAV), and the first column (New TNAV) depends on the second column (via LOOKUPVALUE).
The engine detects this loop and raises a circular dependency error.
How to resolve this:
- You mentioned that your table does not have a unique key. Calculated columns often rely on unique keys to avoid circular dependencies. Consider adding a unique identifier to the table
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-xiandat-msft,
Thank you for your reply and taking the time to explain the nuances of calculated columns - I still have a lot to learn about Power BI and DAX!
Your comment got me thinking about the many-to-one relationship in my factFundReport to factFund tables and, seeing as I only want to find one factFund entry I used the RELATED instead of LOOKUPVALUE, which solved my circular dependency!New Subfunds = VAR SubFundCount = RELATED(factFund[Sub Fund Count]) // LOOKUPVALUE // ( // factFund[Sub Fund Count], // factFund[FundKey], factFundReport[FundKey] // ) RETURN IF(factFundReport[Is New],SubFundCount, 0)
Hoping this can help others stuck on circular dependencies.
