How do I show Billable Category color always (if there any billable category) as conditional for.?

[bikelley]

Hi,
How do I give priority to Billable Primary Category background color?
I am adding background color on a matrix table by a measure ("Duration_Color_New"). I want to show if the title has Billable, then show billable color only. Otherwise, normal colors as listed on the measure.

Titles = 
VAR NewLine =
"
-------------
"
RETURN
CONCATENATEX(VALUES(Tracker_dim[Primary Categories]), Tracker_dim[Primary Categories], NewLine)

Duration_Color_New = 
VAR Duration = SUM(Tracker_dim[Duration_Hr])
 
RETURN (
    IF(MAX(Tracker_dim[Primary Categories]) = "Billable" && Duration >=  8,
        "#92D050" ,
    IF(MAX(Tracker_dim[Primary Categories]) = "Billable" && Duration >=  4,
        "#F4B084", 
    IF(MAX(Tracker_dim[Primary Categories]) = "Billable" && Duration >=  0,
        "#C65911",
    IF(MAX(Tracker_dim[Primary Categories]) = "Hold",
        "#fdee00", 
    IF(MAX(Tracker_dim[Primary Categories]) = "PTO",
         "#00B0F0", "#B4C6E7"))))))

You can see it in the below picture it is not working as expected.
How do I show Billable color always or is there a way to show the color individually? Note - This is a matrix table.

 Thank you

[AllisonKennedy · Accepted solution]

@bikelley I don't know if I 100% understand your question, but you're using 

MAX(Tracker_dim[Primary Categories])

In your colour measure and the Titles allows for multiple values in the Tracker_dim[Primary Categories] column. Using the MAX function means it will look at the last one alphabetically. 

You might be able to get away with changing that to MIN if Billable is the first alphabetically. Otherwise, to be technically more accurate (if I understand your question properly) you need to update your measure to: 

Duration_Color_New =

VAR Duration = SUM(Tracker_dim[Duration_Hr])

RETURN (

IF("Billable" IN VALUES(Tracker_dim[Primary Categories]) && Duration >= 8,

"#92D050" ,

IF("Billable" IN VALUES(Tracker_dim[Primary Categories]) && Duration >= 4,

"#F4B084",

IF("Billable" IN VALUES(Tracker_dim[Primary Categories]) && Duration >= 0,

"#C65911",

IF("Hold" IN VALUES(Tracker_dim[Primary Categories]) = "Hold",

"#fdee00",

IF("PTO" IN VALUES(Tracker_dim[Primary Categories]) = "PTO",

"#00B0F0", "#B4C6E7"))))))

[AllisonKennedy]

@bikelley  This could also be done using SWITCH instead of nested IFs: 

Duration_Color_New =

VAR Duration = SUM(Tracker_dim[Duration_Hr])

RETURN

SWITCH( TRUE()

, "Billable" IN VALUES(Tracker_dim[Primary Categories]) && Duration >= 8,

"#92D050"

, "Billable" IN VALUES(Tracker_dim[Primary Categories]) && Duration >= 4,

"#F4B084"

, "Billable" IN VALUES(Tracker_dim[Primary Categories]) && Duration >= 0,

"#C65911"

, "Hold" IN VALUES(Tracker_dim[Primary Categories]) = "Hold",

"#fdee00"

, "PTO" IN VALUES(Tracker_dim[Primary Categories]) = "PTO",

"#00B0F0", "#B4C6E7"

)

[bikelley]

@AllisonKennedy , 

Thank you so much for taking the time to review my post. I tried creating both nested IF and switch as a measure then try to select it on condition formatting, but it will not allow me to select both of them. Any idea why is that? 

[AllisonKennedy]

@bikelley  Are you able to share a sample file? You can only select one measure to base the conditional formatting off - so everything must be detailed in the one SWITCH condition measure. 

You cannot have two colours in the same cell within a matrix - each Cell can only have one colour.

Hope that helps and makes sense?