Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.