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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CarlsBerg999
Helper V
Helper V

Circular dependency in a calculated column

Hi,

 

I have a table with 2 columns. 

Column A: Customer ID

Column B: Total Sales (complex measure)

 

The Column C would be "Large Customers": IF Total Sales > 100 000€ then "Text 1" else "Text 2".

 

For some reason, column C gives me "A circular dependcy was detected: Table1[Total Sales], Table1[Large Customers], Table1[Total Sales]. How can this be a circular relationship? It's a simple If statement that relies on a fixed 100 000€ comparison.  

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @CarlsBerg999 ,

If your code looks correct, that is if you do not see any obvious circular dependencies, then there are two possible causes for a hidden circular dependency:

  • You are using context transition inside a calculated column.
  • You are creating a relationship that involves either a calculated column or a calculated table.

Context transition in calculated columns. If you do not pay attention to circular dependencies, you cannot create more than one calculated column in a table – if the formula of the column contains CALCULATE anywhere. Indeed, CALCULATE in a calculated column performs a context transition and makes that column dependent on all the columns in the table. If two such columns exist, they depend on each other. Therefore, you experience circular dependency only once you have created the second column.

 

The correct solution to avoid this is to restrict the list of columns that the calculated column depends on, by using ALLEXCEPT or REMOVEFILTERS and keeping only the table’s primary key. If the table has no primary key, then using CALCULATE in a calculated column is dangerous; this is because context transition is likely to produce unexpected results.

 

For more details about circular dependencies, please refer:

  1. Understanding-circular-dependencies 
  2. Avoiding-circular-dependency-errors-in-dax 

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @CarlsBerg999 ,

If your code looks correct, that is if you do not see any obvious circular dependencies, then there are two possible causes for a hidden circular dependency:

  • You are using context transition inside a calculated column.
  • You are creating a relationship that involves either a calculated column or a calculated table.

Context transition in calculated columns. If you do not pay attention to circular dependencies, you cannot create more than one calculated column in a table – if the formula of the column contains CALCULATE anywhere. Indeed, CALCULATE in a calculated column performs a context transition and makes that column dependent on all the columns in the table. If two such columns exist, they depend on each other. Therefore, you experience circular dependency only once you have created the second column.

 

The correct solution to avoid this is to restrict the list of columns that the calculated column depends on, by using ALLEXCEPT or REMOVEFILTERS and keeping only the table’s primary key. If the table has no primary key, then using CALCULATE in a calculated column is dangerous; this is because context transition is likely to produce unexpected results.

 

For more details about circular dependencies, please refer:

  1. Understanding-circular-dependencies 
  2. Avoiding-circular-dependency-errors-in-dax 

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@CarlsBerg999 , You can not use a measure in column calculation .

 

Create this as a new measure

IF Total Sales > 100 000€ then "Text 1" else "Text 2"

 

if need use an independent table if you want to filter

refer video :https://youtu.be/CuczXPj0N-k

or

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

Hi,

 

I was actually trying to do a calculated column but the circular dependency -issue exists. It doesn't let me do a simple If statement for some reason.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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