Dynamic conditional formatting based on values of field

[justnoob08]

I'm trying to create a conditional format but it is dynamic based on the row value

I'm trying to display this in a matrix with a stoplight analysis, My challenge is that the rule is different for each "Page" I have tried using measure but am unable to do so, I can only achieve 1 condition for all pages.

Page	Airline 1	Airline 2	Airline 3
LoadTime					Per Page Stoplight Analysis conditional format
Homepage	7.22	5.15	4.53		< 3 Seconds = Green, 3.99 to 11 seconds = yellow, >12 Seconds = Red
FlightSelect	8.11	15.36	14.14		< 5 Seconds = Green, 5.99 to 19 seconds = yellow, >19 Seconds = Red
Guest	6.66	5.89	6.32		< 6 Seconds = Green, 6.99 to 24 seconds = yellow, > 24 Seconds = Red
Addons	9.79	14.55	12.7		< 3 Seconds = Green, 3.99 to 11 seconds = yellow, >12 Seconds = Red
Booking Summary	5.16	16.95	15.46		< 5 Seconds = Green, 5.99 to 19 seconds = yellow, >19 Seconds = Red

and My data is something like this

Date	Airline	LoadTime	Page
11/6/2023	Airline 1	7.22	Homepage
11/6/2023	Airline 2	5.15	Homepage
11/6/2023	Airline 3	4.53	Homepage
11/6/2023	Airline 1	8.11	FlightSelect
11/6/2023	Airline 2	15.36	FlightSelect
11/6/2023	Airline 3	14.14	FlightSelect
11/6/2023	Airline 1	6.66	Guest
11/6/2023	Airline 2	5.89	Guest
11/6/2023	Airline 3	6.32	Guest
11/6/2023	Airline 1	9.79	Addons
11/6/2023	Airline 2	14.55	Addons
11/6/2023	Airline 3	12.7	Addons
11/6/2023	Airline 1	5.16	Booking Summary
11/6/2023	Airline 2	16.95	Booking Summary
11/6/2023	Airline 3	15.46	Booking Summary

[isjoycewang · Accepted solution]

Hi @justnoob08,

Please apply below measure to your table background/font color.

*Note - I just write several scenario for reference so please remember to write definition for other pages.

Color Measure = 
VAR _loadtime = SUM('Table'[LoadTime])
RETURN
SWITCH(TRUE(),
SELECTEDVALUE('Table'[Page]) = "Homepage" && _loadtime<3, "Green",
SELECTEDVALUE('Table'[Page]) = "Homepage" && _loadtime >3.99 && _loadtime<11, "Yellow",
SELECTEDVALUE('Table'[Page]) = "Homepage" && _loadtime>12, "Red",
SELECTEDVALUE('Table'[Page]) = "FlightSelect" && _loadtime<5, "Green",
SELECTEDVALUE('Table'[Page]) = "FlightSelect" && _loadtime >5.99 && _loadtime<19, "Yellow",
SELECTEDVALUE('Table'[Page]) = "FlightSelect" && _loadtime>19, "Red",
SELECTEDVALUE('Table'[Page]) = "Guest" && _loadtime<6, "Green",
SELECTEDVALUE('Table'[Page]) = "Guest" && _loadtime>6.99 && _loadtime<24, "Yellow",
SELECTEDVALUE('Table'[Page]) = "Guest" && _loadtime>24, "Red")

Best Regards,

Joyce

[justnoob08]

Hi, Joyce. 

Thank you for the response I was able to confirm this as working. Do you happen to know if I can use the actual stoplight Icons for "Green", "Yellow", "Red" like below?

[isjoycewang]

Hi @justnoob08,

If you want to have a status icon in your table, please consider to create below measure and apply conditional formatting on the font color of it.

Status = UNICHAR(11044)
// Apply Conditional formatting by field value on [Status Code]