Conditional formatting of a cell in a row of the matrix

[powerbiuser444]

Hi all,

I can't figure out why my measure doesn't work properly. I'm using a measure for background conditional formatting of a cell based on certain conditions (I'm comparing values between W0, W1, and W2). If it's the lowest it should be red, highest - green, in the middle - yellow.

This is my DAX code

Formatting_Value1 =

VAR ValueW0 = CALCULATE(SUM(Data[Value1]),Data[date]="W0")

VAR ValueW1 = CALCULATE(SUM(Data[Value1]),Data,Data[date]="W1")

VAR ValueW2 = CALCULATE(SUM(Data[Value1]),Data,Data[date]="W2")

RETURN

SWITCH(TRUE(),

ValueW0 < ValueW1 && ValueW0 < ValueW2 && SELECTEDVALUE(Data[date])="W0", "red",

ValueW0 <= ValueW1 && ValueW0 >= ValueW2 && SELECTEDVALUE(Data[date])="W0", "yellow",

ValueW0 >= ValueW1 && ValueW0 <= ValueW2 && SELECTEDVALUE(Data[date])="W0", "yellow",

ValueW0 > ValueW1 && ValueW0 > ValueW2 && SELECTEDVALUE(Data[date])="W0", "green", BLANK())

The logic says it should execute only if all conditions are met, but it seems it takes into account only SELECTEDVALUE(Data[date])="W0" because the color is always green, which is not correct. Please help to fix this or suggest another logic.

I'm attaching pbix  in case if it's more helpful 

[powerbiuser444]

I fixed myself, it should be in the brackets

SWITCH(TRUE(),

ValueW0 < ValueW1 && ValueW0 < ValueW2 && SELECTEDVALUE(Data[date])="W0", "red",

( ValueW0 <= ValueW1 && ValueW0 >= ValueW2 ) && SELECTEDVALUE(Data[date])="W0", "yellow",

( ValueW0 >= ValueW1 && ValueW0 <= ValueW2 ) && SELECTEDVALUE(Data[date])="W0", "yellow",

ValueW0 > ValueW1 && ValueW0 > ValueW2 && SELECTEDVALUE(Data[date])="W0", "green", BLANK())

[Greg_Deckler · Accepted solution]

@powerbiuser444 Weird, I just fixed it but had to use ALL:

Formatting_Value1 = 
VAR ValueW0 = CALCULATE(SUM(Data[Value1]),ALL(Data),Data[date]="W0")
VAR ValueW1 = CALCULATE(SUM(Data[Value1]),ALL(Data),Data[date]="W1")
VAR ValueW2 = CALCULATE(SUM(Data[Value1]),ALL(Data),Data[date]="W2")

RETURN
SWITCH(TRUE(),
ValueW0 < ValueW1 && ValueW0 < ValueW2 && SELECTEDVALUE(Data[date])="W0", "red",
ValueW0 <= ValueW1 && ValueW0 >= ValueW2 && SELECTEDVALUE(Data[date])="W0", "yellow",
ValueW0 >= ValueW1 && ValueW0 <= ValueW2 && SELECTEDVALUE(Data[date])="W0", "yellow",
ValueW0 > ValueW1 && ValueW0 > ValueW2 && SELECTEDVALUE(Data[date])="W0", "green", BLANK())

[powerbiuser444]

@Greg_Deckler actually you are right, thanks a lot

[powerbiuser444]

Hi @Greg_Deckler I changed the link, please check now

[Greg_Deckler]

@powerbiuser444 Couldn't access that link. First thing to try, in your measure try returning ValueW1 for example and return that as a value in your matrix versus background color and see if it shows up correctly in the W0 column. That way at least you will know if the calculation for that variable is correct or not within the filter context provided by the visual.