Conditional Color Shade Format for Each Cell in a Row in a Table

[Dawg]

I need to conditionally format each row in a table differently. In the example table below, the threshold for what is "red", "yellow", or "green" is different for each row. 

How do I conditionally format each row so that the threshold colors are properly captured for each row? 

[Nasif_Azam]

Hey @Dawg ,

To conditionally format each row in Power BI’s Table visual with row-specific thresholds, follow these steps using DAX and conditional formatting rules:

Understanding the Problem

In your table:

• Each row (Item) has its own threshold for Red, Yellow, and Green.

• You want to apply background color formatting to the 2024 and 2025 values based on that row’s threshold.

Solution Overview

Power BI’s built-in conditional formatting does not directly support row-level thresholds, so we solve this by:

1. Creating custom DAX measures or columns that return hex color codes based on row-specific thresholds.

2. Applying those values to the background color in the visual using Field value formatting.

Steps to Implement

Step 1: Reshape Your Table to Long Format (Optional but Recommended)

Use Power Query to convert:
| Item | Threshold Red | Threshold Yellow | Threshold Green | 2024 | 2025 |

into:
| Item | Year | Value | Threshold Red | Threshold Yellow | Threshold Green |

Step 2: Create a New Column or Measure for Color

In DAX, create a calculated column or measure that checks the year-specific value against the row-specific thresholds.

Example (for a column format like Value, Item, Year, Threshold_Red, etc.):

Color Code = 
SWITCH(TRUE(),
    'Table'[Value] >= 'Table'[Threshold_Red], "#FF0000",       // Red
    'Table'[Value] = 'Table'[Threshold_Yellow], "#FFFF00",     // Yellow
    'Table'[Value] <= 'Table'[Threshold_Green], "#00FF00",     // Green
    "#FFFFFF" // Default white
)

You can also break this into two separate measures if needed — one for 2024 and one for 2025 — by applying a filter inside a measure.

Step 3: Apply Conditional Formatting in Table Visual

1. Select your Table visual.

2. Go to the column for 2024 or 2025.

3. Click More options (…) > Conditional Formatting > Background color.

4. Choose “Format by: Field value”.

5. Set the field to your custom DAX column or measure (Color Code).

Repeat for each year if needed.

Things To Remember:

• You must use a table visual, not a matrix or card.

• For multiple years, creating separate color measures like Color_2024, Color_2025 might simplify application.

• Ensure your data model is properly normalized for dynamic scaling.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[grognard]

 Hello @Dawg I’d recommend approaching this in three steps:

1) Add a column for each of the threshold values, this would mean adding a column for red to each  your table becomes 

	Red	Yellow	Green	2024	2025
Item 1	3	2	1	3	2
Item 2	15	14	13	13	15
Item 3	30	20	10	30	10

2) Second step is to make a calculated column that determines the final color, this can be done using a switch statement like this:

_2024color = SWITCH(TRUE, 
    /* replace Query1 with your table name */
    Query1[2024] <= Query1[Green],"green",
    Query1[2024] <= Query1[Yellow],"yellow",
    Query1[2024] <= Query1[Red],"red"
)

since you are using two columns here, you’ll have to add one for each value you want to color.

> Note: you can also use hex colors instead of the color name 

3) Finally you can use conditional formatting to color the actual text box, select the column you want to change, enable conditional formatting, change the formatting style to “Field Value”, and set the value of “What field should we base this on?” to the calculated column we made in step 2. 

Here's a diagram of the steps

You could also use formatting to change the color of other kinds of charts if you like.

[Jai-Rathinavel]

Hi @Dawg  Can you provide me a Sample data to work with ? So that I can provide you the DAX accordingly 

Thanks,

Jai Rathinavel | LinkedIn