Forum Discussion

Dawg's avatar
Dawg
Frequent Visitor
1 year ago

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

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 conditionall...
Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

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