Forum Discussion
Dynamic conditional formatting based on values of field
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 |
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
3 Replies
isjoycewangSolution Supplier
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
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]
