Matrix table conditional formatting based on category and value - Dax coding?

[JoeVincent]

Hi there, 

I am at the final stage of my dashboard and I'm struggling to create conditional formatting based on multiple parameters and could really do with some help please.

I have attached a mocked up table of how my table appears. I am looking to create conditional formatting based on Grade and the specific value based on utilisation expectations for that grade. I've chosen to use mock data just to represent the data and not risk any issues with data protection/GDPR.

Grade expecations:
Director: 50+ Green, 40-50 Amber, <40 Red
Senior Manager: 65+ Green, 55-65 Amber, <55 Red

Manager, Senior Associate and Associate: 75+ Green, 65-75 Amber, <65 Red

(Merged cells were taken out when this was posted. For reference, there is a PW, P4W and YTD column under each grade. Sorry!)

Grade	1 Director	2 Senior Manager	3 Manager	4 Senior Associate	5 Associate
LoS	PW	P4W	YTD	PW	P4W	YTD	PW	P4W	YTD	PW	P4W	YTD	PW	P4W	YTD
X	40	50	60	80	75	80	65	90	70	60	50	90	70	40	80
Y	50	50	60	60	70	65	80	75	80	80	60	80	90	60	50
Z	20	40	70	40	45	70	70	80	75	90	70	60	100	85	60

Thank you so much in advance!

Screen snip of example table:

I've used this DAX expression for a matrix table in a different dashboard, was wondering if this could be repurposed?

Colour by category =

   SWITCH (TRUE (),

     MAX('TL Forecast Bookings'[Booking Type]) = "Chargeable", "LightGreen",

     MAX('TL Forecast Bookings'[Booking Type]) = "Provisional/Reserved", "Orange",

     MAX('TL Forecast Bookings'[Booking Type]) = "Vacation/Holiday", "Yellow",

     MAX('TL Forecast Bookings'[Booking Type]) = "Training/Exams", "Violet",

     MAX('TL Forecast Bookings'[Booking Type]) = "Non Chargeable", "DodgerBlue",

     MAX('TL Forecast Bookings'[Booking Type]) = "Internal Project", "DodgerBlue"

)

[Padycosmos]

Hope this video helps:

https://www.youtube.com/watch?v=7veYU1EeO6w

[NishPatel]

You can use cell elements property under visual property to conditionally format each series. 

[JoeVincent]

Thanks for the quick response Nish. That seems to apply it for PW/P4W/YTD irrespective of Grade unless I am missing something?

[amitchandak]

@JoeVincent , I doubt you can do conditional formatting for row/column fields. You can do series-based colors or column-based

for values, you can use conditional formatting. BUT not for row/column

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...

[JoeVincent]

I've used the below dax expression to colour different values based on different conditions in a separate dashboard. Wondering if this can be repurposed for this?

Colour by category =

   SWITCH (TRUE (),

     MAX('TL Forecast Bookings'[Booking Type]) = "Chargeable", "LightGreen",

     MAX('TL Forecast Bookings'[Booking Type]) = "Provisional/Reserved", "Orange",

     MAX('TL Forecast Bookings'[Booking Type]) = "Vacation/Holiday", "Yellow",

     MAX('TL Forecast Bookings'[Booking Type]) = "Training/Exams", "Violet",

     MAX('TL Forecast Bookings'[Booking Type]) = "Non Chargeable", "DodgerBlue",

     MAX('TL Forecast Bookings'[Booking Type]) = "Internal Project", "DodgerBlue"

)