Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bexbissell
Frequent Visitor

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:

bexbissell_1-1708529475877.png


When I create a second calculated column on the same table I get the circular dependency error:

bexbissell_2-1708529540059.png

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:

bexbissell_3-1708529667446.png


Any useful pointers in the right direction or tool suggestions are much appreciated.


Many thanks.




1 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.

View solution in original post

2 REPLIES 2
v-xiandat-msft
Community Support
Community Support

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.