Circular dependency on calculated column

[bexbissell]

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.

[Anonymous]

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.

[bexbissell · Accepted solution]

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.