Forum Discussion
Circular Reference in Conditional Formatting columns
kekepania0529 You're using a Measure inside a Calculated column, which adds the CALCULATE function to your calculated column.
Short answer (how to fix):
Add CALCLULATE( [measure], ALLEXCEPT(Table, Table[Key])) anywhere you reference a measure.
Do this in all your calculated columns anywhere you have a measure, and click into existing Calculated columns and hit Enter to force them to re-evaluate (Circular Reference errors don't fix on their own).
Long answer (why):
This post explains it well: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/
But my recap of it is:
The CALCULATE function converts ROW context into FILTER context, which means that even though you don't see it, every single column in your table that already exists is written (in invisible ink shall we say??? 🤣 ) in your formula.
So, you can create the first column with no issues, let's say it's called [Color Font] and the formula is simple
[Color Font] = [Measure]
Your table now looks like this:
Category Product Color Font Fruit Pear Green Fruit Orange Orange Vegetable Carrot Orange Vegetable Pepper Green The [Color Font] column uses a Measure, so that has an implicit CALCULATE function wrapped around the measure, which adds the following code, different for each row:
Category Product Color Font Color Font Code Fruit Pear Green [Color Font] = CALCULATE( [Measure], Table[Category] = "Fruit", Table[Product] = "Pear") Fruit Orange Orange [Color Font] = CALCULATE( [Measure], Table[Category] = "Fruit", Table[Product] = "Orange") Vegetable Carrot Orange [Color Font] = CALCULATE( [Measure], Table[Category] = "Vegetable", Table[Product] = "Carrot") Vegetable Pepper Green [Color Font] = CALCULATE( [Measure], Table[Category] = "Vegetable", Table[Product] = "Pepper") But now you want to add a second column, [Color Background]. By adding another column, the code of our original column will change, and so will the [Color Background] code, and you'll see that they both try to reference each other, thus resulting in Circular Reference Error:
Category Product Color Font Color Background Color Font Code Color Background Code Fruit Pear Green [Color Font] = CALCULATE( [Measure], Table[Category] = "Fruit", Table[Product] = "Pear", Table[Color Background] = "Value Color Background") [Color Background] = CALCULATE( [Measure], Table[Category] = "Fruit", Table[Product] = "Pear", Table[Color Font] = "Value Color Font") Fruit Orange Orange [Color Font] = CALCULATE( [Measure], Table[Category] = "Fruit", Table[Product] = "Orange", Table[Color Background] = "Value Color Background") [Color Background] = CALCULATE( [Measure], Table[Category] = "Fruit", Table[Product] = "Orange", Table[Color Font] = "Value Color Font") Vegetable Carrot Orange [Color Font] = CALCULATE( [Measure], Table[Category] = "Vegetable", Table[Product] = "Carrot", Table[Color Background] = "Value Color Background") [Color Background] = CALCULATE( [Measure], Table[Category] = "Vegetable", Table[Product] = "Carrot", Table[Color Font] = "Value Color Font") Vegetable Pepper Green [Color Font] = CALCULATE( [Measure], Table[Category] = "Vegetable", Table[Product] = "Pepper", Table[Color Background] = "Value Color Background") [Color Background] = CALCULATE( [Measure], Table[Category] = "Vegetable", Table[Product] = "Pepper", Table[Color Font] = "Value Color Font") To fix it, we could use the Unique column identifier (in this example not ideal but the Product name). So if we rewrite our [Color Font] column as:
[Color Font] = CALCULATE([Measure], ALLEXCEPT(Table, Table[Product]) )
and do the same for Color Background, then we'll get rid of the Circular Dependency.
kekepania0529 As a side note, could you create this as a measure instead of a Calculated column? That would also fix the problem... Just need to figure out what context to evaluate within in that case, so you might find my original suggestion easier.