Circular Reference in Conditional Formatting columns

[kekepania0529]

I need to color the font and background of a measure based on the same conditions.

I created a new column to use for the background conditional format.

• Color Background GT/LB per Load = IF(AND([GT/LB per Load]<35000,LEFT('Inbound Detail'[Product Group],3)="Non"),"#FFE5CC",BLANK())&IF(AND([GT/LB per Load]<15,LEFT('Inbound Detail'[Product Group],2)="Fe"),"#FFCCCC",BLANK())

When I attempt to make another column for conditional formatting the font, copying the syntax of the 1st column, I am getting a circular dependency error on the 2nd column.

• Color Font GT/LB per Load = IF(AND([GT/LB per Load]<35000,LEFT('Inbound Detail'[Product Group],3)="Non"),"#CC0000",BLANK())&IF(AND([GT/LB per Load]<15,LEFT('Inbound Detail'[Product Group],2)="Fe"),"#CC0000",BLANK())

I don't understand why the syntax works for the 1st column, but not the 2nd column. 

Any idea how I remedy the circular dependency error?

[AllisonKennedy]

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

[AllisonKennedy · Accepted solution]

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