Conditional Formatting in Power BI Matrix Based on Dynamic KRI Status Thresholds

[KL008]

Hi everyone,

I'm working on a Power BI report and need some help with conditional formatting in a matrix visual. I have two tables: KRI and KRI results, connected by a one-to-many relationship between KRI[ID] and KRI results[Id].

In the KRI table, there are four columns of interest: "Grenswaarde Rood", "Grenswaarde Oranje", "Grenswaarde Geel", and "Grenswaarde Groen". These columns specify the status thresholds for each KRI. For example, for KRI with Id = 9, the thresholds are:

• Red: <6% or >20%
• Orange: 6-8% or 18-20%
• Yellow: 8-10% or 16-18%
• Green: 10-16%

But the thresholds for another KRI will be different since they are measuring different results and information.

I want to display the KRIs from the KRI results table in a matrix where:

• Rows: KRI name
• Columns: Organization level
• Values: Title (which contains the results)

I need the background color of the cells to change based on the status thresholds specified in the KRI table. However, each KRI has different thresholds, so the formatting needs to be dynamic.

Does anybody have any tips and tricks for this?

[Anonymous]

Hi @KL008, hello Poojara_D12  and bhanu_gautam, thank you for your prompt reply!

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

[KL008]

KRI Results:

ID	KRI ID	Result	Department	Quarter
1	70	8	Marketing	1
2	70	4	Sales	1
3	70	19	IT	1
4	71	5	Marketing	1
5	71	8	Sales	1
6	71	3	IT	1
7	70	6	Marketing	2
8	70	7	Sales	2
9	70	7.5	IT	2
10	71	4	Marketing	2
11	71	5.5	Sales	2
12	71	7	IT	2

KRI Info:

ID	KRI ID	KRI Name	Red	Orange	Yellow	Green
1	70	% retained	<6 or >20	6-8 or 18-20	8-10 or 16-18	10-16
2	71	% absent	>= 7	6-7	4.5-6	<4.5

In my matrix I use KRI results table. In the rows I have KRI ID, in the columns I have department and in the Values I have result. Is it possible to implement conditional formatting here?

[Anonymous]

Hi @KL008 , thank you for your sample data.

Based on my tests and research, there doesn't appear to be an out-of-the-box solution for this requirement.

The challenge lies in the need to dynamically obtain and apply different formatting rules for various colors within a single measure. Each color has its own unique formatting rule, which adds to the complexity.

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Poojara_D12]

Hi @KL008 

To dynamically format the matrix cells based on KRI thresholds:

1. Create a DAX Measure: Write a measure to calculate the status (Red, Orange, Yellow, Green) based on the KRI table thresholds:

Status = 
VAR Value = MAX('KRI results'[Result])
VAR RedLow = LOOKUPVALUE('KRI'[Red Low], 'KRI'[ID], MAX('KRI results'[Id]))
VAR RedHigh = LOOKUPVALUE('KRI'[Red High], 'KRI'[ID], MAX('KRI results'[Id]))
-- Add similar variables for Orange, Yellow, Green
RETURN
    SWITCH(
        TRUE(),
        Value < RedLow || Value > RedHigh, "Red",
        -- Add conditions for Orange, Yellow, Green
        "Green"
    )

• Add to Matrix: Use the Status measure in the matrix as the Values field.
• Apply Conditional Formatting:
  • Format the Title column in the matrix with conditional formatting.
  • Use the Field value option and select the Status measure.
  • This will color cells dynamically based on KRI-specific thresholds.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[KL008]

Hi Poojara, again thanks for you insight but it doesn't work here for me. The boundary for red can be either <6 or >20.

[bhanu_gautam]

@KL008 , First, you need to create a measure that will determine the color based on the thresholds for each KRI. This measure will use the thresholds from the KRI table to evaluate the results in the KRI results table.

DAX
KRI Color =
VAR KRI_ID = SELECTEDVALUE('KRI results'[Id])
VAR Result = SELECTEDVALUE('KRI results'[Result])
VAR RedMin = LOOKUPVALUE('KRI'[Grenswaarde Rood Min], 'KRI'[ID], KRI_ID)
VAR RedMax = LOOKUPVALUE('KRI'[Grenswaarde Rood Max], 'KRI'[ID], KRI_ID)
VAR OrangeMin = LOOKUPVALUE('KRI'[Grenswaarde Oranje Min], 'KRI'[ID], KRI_ID)
VAR OrangeMax = LOOKUPVALUE('KRI'[Grenswaarde Oranje Max], 'KRI'[ID], KRI_ID)
VAR YellowMin = LOOKUPVALUE('KRI'[Grenswaarde Geel Min], 'KRI'[ID], KRI_ID)
VAR YellowMax = LOOKUPVALUE('KRI'[Grenswaarde Geel Max], 'KRI'[ID], KRI_ID)
VAR GreenMin = LOOKUPVALUE('KRI'[Grenswaarde Groen Min], 'KRI'[ID], KRI_ID)
VAR GreenMax = LOOKUPVALUE('KRI'[Grenswaarde Groen Max], 'KRI'[ID], KRI_ID)

RETURN
SWITCH(
TRUE(),
Result < RedMin || Result > RedMax, "Red",
(Result >= OrangeMin && Result <= OrangeMax), "Orange",
(Result >= YellowMin && Result <= YellowMax), "Yellow",
(Result >= GreenMin && Result <= GreenMax), "Green",
"No Color"
)

 Once you have the measure, you can apply it to the matrix visual for conditional formatting.

Select the matrix visual.
Go to the "Format" pane.
Expand the "Conditional formatting" section.
Choose the field you want to format (e.g., "Title").
Click on "Background color" or "Font color".
In the conditional formatting dialog, choose "Field value" and select the measure you created (e.g., KRI Color).
Adjust the Measure for Your Specific Thresholds: Ensure that the measure correctly references the threshold columns in your KRI table. If your thresholds are stored differently (e.g., as ranges in a single column), you may need to adjust the measure accordingly.

[KL008]

Hi bhanu,
Thanks for getting back to me and it would be a great solution, however, some of my boundaries are two sided. What I mean by this is that e.g. KPI1 has the boundaries for red of either <6 or >20. So your solution doesn't work here, otherwise it would be great!