Unable to Add Conditional Formatting to a Measure

[GeekyT]

I have created a measure to tell me whether a task is overdue based on it's due date. This is the DAX I used:

State = IF(MIN('wkaw'[Due Date])<TODAY(),"1",

(IF(MIN('wkaw'[Due Date])>=TODAY(),"2",

(IF(MIN('wkaw'[Due Date])>=TODAY()+7,"3"

)))))

Where 1 is overdue, 2 is due this week and 3 is due after more than a week.

It works perfectly well without any errors.

However I'd like the 'State' to change colour dependant on it's value, so I tried to set up

conditional formatting but when I try to select State from the 'Based on Field' list to format

it won't let me select it. For every other field, when you hover the pointer over the name, it goes

grey to indicate it can be selected but not my measure.

Has anyone else come across this? I can't find any information online to suggest there are particular

conditions a field must meet before you can apply conditional formatting.

[MFelix]

Hi @GeekyT ,

The condittional formatting based on field value means that you have to set the colour that you need on your measure in this case yuou need to redo your measure to one of the formulas below.

One thing that I believe is also best is instead of havinf nested IF is to use SWITCH function.

State =
SWITCH (
    TRUE (),
    MIN ( 'wkaw'[Due Date] ) < TODAY (), "Red",
    MIN ( 'wkaw'[Due Date] ) >= TODAY (), "Green",
    MIN ( 'wkaw'[Due Date] )
        >= TODAY () + 7, "Blue"
)



State =
SWITCH (
    TRUE (),
    MIN ( 'wkaw'[Due Date] ) < TODAY (), "#00000",
    MIN ( 'wkaw'[Due Date] ) >= TODAY (), "#123456",
    MIN ( 'wkaw'[Due Date] )
        >= TODAY () + 7, "#963852"
)

The first measure you can write the colour you need directly, on the second one I'm using the HEX code so you can change the 6 digits in each one for the custom colours you want.

Then you canuse the based in value formula for the conditional formatting.

[amitchandak · Accepted solution]

@GeekyT , Not very clear, Create a color measure and use that in conditional formatting with "Field value"

State = Switch( true(),
MIN('wkaw'[Due Date])<TODAY(),"green",
MIN('wkaw'[Due Date])>=TODAY(),"yellow",
MIN('wkaw'[Due Date])>=TODAY()+7,"red"
)

refer https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[GeekyT]

@amitchandak Fantastic! I was unaware of colour measures. Works perfectly.