Circular dependency in a calculated column

[CarlsBerg999]

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.  

[v-yingjl · Accepted solution]

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]

@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

[CarlsBerg999]

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.