Conditional formatting with DAX in case of SWITCH measure

[vandavarosi]

Hi, 

I have a measure which are connected with a period filter. Based on what I am filtering I am able to see the data (e.g. for 1MO or 3MO). 

Based on the magnitude of the numbers (positive or negative), I would like to highlight the background of the cells with green or red. The numbers are splitted by region. (e.g. if the 1MO value is negative for Asia the background of the cell should be red, however, if I change the filter for 3MO and the value is positive the color of the cell should change to green). 

This is the view what I would like to change. 

And this is the original measure. 

Can you please help?

Thanks, 

Vanda

[Anonymous]

The Color field for conditional formatting requires a STRING. Even if you set each measure and your SWITCH() measure to Text, it will still not work.

TO FIX: You can assure each measure is a STRING by wrapping each measure in CONVERT([measure], STRING)

vs LY slicer =
IF(
ISCROSSFILTERED('MS Period slicer'[MS period name]),
SWITCH(
TRUE(),
VALUES('MS Period slicer'[MS period name]) = "1 MO view", CONVERT([1MO vs LY in bps], STRING),
VALUES('MS Period slicer'[MS period name]) = "3 MO view", CONVERT([3MO vs LY in bps], STRING),
VALUES('MS Period slicer'[MS period name]) = "6 MO view", CONVERT([6MO vs LY in bps], STRING),
VALUES('MS Period slicer'[MS period name]) = "12 MO view", CONVERT([12MO vs LY in bps], STRING),
VALUES('MS Period slicer'[MS period name]) = "FYTD view", CONVERT([FYTD MO vs LY in bps], STRING),
BLANK()
),
BLANK()
)

You can verify that text-only works, and you can load thiss measure into the Color conditional format, with random colors like this:

vs LY slicer =
IF(
ISCROSSFILTERED('MS Period slicer'[MS period name]),
SWITCH(
TRUE(),
VALUES('MS Period slicer'[MS period name]) = "1 MO view", "#FF5733", // Red-Orange
VALUES('MS Period slicer'[MS period name]) = "3 MO view", "#33FF57", // Green
VALUES('MS Period slicer'[MS period name]) = "6 MO view", "#3357FF", // Blue
VALUES('MS Period slicer'[MS period name]) = "12 MO view", "#F0FF33", // Yellow
VALUES('MS Period slicer'[MS period name]) = "FYTD view", "#FF33F0", // Pink
"#808080" // Default Gray
),
"#808080"
)

[amitchandak · Accepted solution]

@vandavarosi , You can create a color measure that return color based on condition and use that in conditional formatting using field value option

examples

Color = Switch( True() ,
Max(Table[Status]) < 200 ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
) ,
Max(Table[Status]) < 500 ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
) ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
)
)

Colour =
SWITCH(TRUE(),
'Table'[Date] < TODAY(), "red",
'Table'[Date] = TODAY(), "orange",
"green")

color Measure =
Switch(True() ,
max(Table[ID]) in {10,20,30} ,"Red",
max(Table[ID]) in {5,15,25} ,"Blue",
"White"
)

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...