topic Re: Conditional formatting of a cell in a row of the matrix in DAX Commands and Tips

Conditional formatting of a cell in a row of the matrix

powerbiuser444 — Tue, 03 Aug 2021 18:46:25 GMT

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

Re: Conditional formatting of a cell in a row of the matrix

Greg_Deckler — Mon, 02 Aug 2021 01:18:01 GMT

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.

Re: Conditional formatting of a cell in a row of the matrix

powerbiuser444 — Tue, 03 Aug 2021 18:47:10 GMT

Hi Greg_Deckler I changed the link, please check now

Re: Conditional formatting of a cell in a row of the matrix

powerbiuser444 — Wed, 04 Aug 2021 04:25:34 GMT

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())

Re: Conditional formatting of a cell in a row of the matrix

Greg_Deckler — Wed, 04 Aug 2021 13:15:40 GMT

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())

Re: Conditional formatting of a cell in a row of the matrix

powerbiuser444 — Wed, 04 Aug 2021 21:18:49 GMT

Greg_Deckler actually you are right, thanks a lot