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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
justnoob08
Frequent Visitor

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.

PageAirline 1Airline 2Airline 3  
LoadTime    Per Page Stoplight Analysis conditional format
Homepage7.225.154.53 < 3 Seconds = Green, 3.99 to 11 seconds = yellow, >12 Seconds = Red
FlightSelect8.1115.3614.14 < 5 Seconds = Green, 5.99 to 19 seconds = yellow, >19 Seconds = Red
Guest6.665.896.32 < 6 Seconds = Green, 6.99 to 24 seconds = yellow, > 24 Seconds = Red
Addons9.7914.5512.7 < 3 Seconds = Green, 3.99 to 11 seconds = yellow, >12 Seconds = Red
Booking Summary5.1616.9515.46 < 5 Seconds = Green, 5.99 to 19 seconds = yellow, >19 Seconds = Red


and My data is something like this

DateAirlineLoadTimePage
11/6/2023Airline 17.22Homepage
11/6/2023Airline 25.15Homepage
11/6/2023Airline 34.53Homepage
11/6/2023Airline 18.11FlightSelect
11/6/2023Airline 215.36FlightSelect
11/6/2023Airline 314.14FlightSelect
11/6/2023Airline 16.66Guest
11/6/2023Airline 25.89Guest
11/6/2023Airline 36.32Guest
11/6/2023Airline 19.79Addons
11/6/2023Airline 214.55Addons
11/6/2023Airline 312.7Addons
11/6/2023Airline 15.16Booking Summary
11/6/2023Airline 216.95Booking Summary
11/6/2023Airline 315.46Booking Summary
1 ACCEPTED SOLUTION
isjoycewang
Super User
Super User

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

 

 

isjoycewang_0-1699264638837.pngisjoycewang_1-1699264676678.png

 

Best Regards,

Joyce

View solution in original post

3 REPLIES 3
isjoycewang
Super User
Super User

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

 

 

isjoycewang_0-1699264638837.pngisjoycewang_1-1699264676678.png

 

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?

 

justnoob08_0-1699318100583.png

 

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]

 

isjoycewang_0-1699322543249.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.