cancel
Showing results for
Did you mean:

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

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.

Thank you

1 ACCEPTED SOLUTION
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"))))))

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.

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

4 REPLIES 4
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"))))))

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.

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

Super User

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

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.

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

Helper IV

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?

Super User

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

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.

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors