cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bikelley
Helper IV
Helper IV

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

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.

Untitled.jpg

 Thank you

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@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"))))))

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

4 REPLIES 4
AllisonKennedy
Super User
Super User

@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"))))))

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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"
)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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? 

@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?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors