Conditional Formatting

[Anonymous]

I have 4 Measures from 4 Diffrent tables. I need to Add those 4 Measures and Show in a Matrix Table as month Year as Columns .  I have Added those and able to Display in a Matrix Table . Now I am facing challenge in Conditional Formatting Background Colour . I need to change the Background colour to Red Green or Amber Based on the Threshold . Here the Scenario is The 4 Measures has 4 Diffrent Threshold , If any of the Measure not meet the Threshold then The Cell Becomes that Color .

For Example :

Measure 1 : 1-5 then Green ,6-10 then Amber,>10 then Red
Measure 2 : 1-100 then Green,100-1000 then Amber ,>1000 then Red
measure 3 : 0-1 then Green , 1-2 then Amber , >2 then Red

if any of these measures Amber the Entire cell should be Amber If any of the Measure is Red then the Cell should Be red. 

Please help me to find a solution for this . I am stuck here . 

Regards,

Shinooj M

[Anonymous]

Hi @Anonymous ,

Please create another new measure as below to get it:

Measure =
SWITCH (
    TRUE (),
    [Measure 1] >= 1
        && [Measure 1] <= 5
        || [Measure 2] >= 1
        && [Measure 2] <= 100
        || [Measure 3] >= 0
        && [Measure 3] <= 1, "Green",
    [Measure 1] >= 6
        && [Measure 1] <= 10
        || [Measure 2] >= 100
        && [Measure 2] <= 1000
        || [Measure 3] > 1
        && [Measure 3] <= 2, "Amber",
    [Measure 1] > 10
        || [Measure 2] > 1000
        || [Measure 3] > 2, "Red"
)

And refer the following official documentation to apply conditional formatting:

Apply conditional formatting in tables and matrixes

Best Regards

[Anonymous]

Please find the below Condition . Which is not giving Expected Output . rather Its Giving Alll the Cells Green

Color Condition 3 = SWITCH(

TRUE(),

Ticket[Incident Count] >= 0

&& Ticket[Incident Count] < [Average Incidents Logged]

|| Ticket[Monitoring Incident]>=0

&& Ticket[Monitoring Incident]<[Average Incidents Logged Monitoring]

|| [Problem Count] >= 0

&& [Problem Count] <=5

|| Risk[Risk Count] >= 0

&& Risk[Risk Count] <= 5

|| VW_OUTAGE[Outagecount] >=0

&& VW_OUTAGE[Outagecount] <= 5 , "Green",

Ticket[Incident Count] >= [Average Incidents Logged]

&& Ticket[Incident Count] < [Average+20]

|| Ticket[Monitoring Incident]>=[Average Incidents Logged Monitoring]

&& Ticket[Monitoring Incident]<[Average Monitoring+20]

|| [Problem Count] >= 6

&& [Problem Count] <=10

|| Risk[Risk Count] >= 6

&& Risk[Risk Count] <= 10

|| VW_OUTAGE[Outagecount] >=6

&& VW_OUTAGE[Outagecount] <= 10 , "Amber",

Ticket[Incident Count] >= [Average+20]

|| Ticket[Monitoring Incident]>=[Average Monitoring+20]

|| [Problem Count] >= 11

|| Risk[Risk Count] >= 11

|| VW_OUTAGE[Outagecount] >=11 , "Red"

)

Regards,

Shinooj M

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please update the formula of measure [Color Condition 3] as below and check if it can return the expected result...

Color Condition 3 =
SWITCH (
    TRUE (),
    ( Ticket[Incident Count] >= 0
        && Ticket[Incident Count] < [Average Incidents Logged] )
        || ( Ticket[Monitoring Incident] >= 0
        && Ticket[Monitoring Incident] < [Average Incidents Logged Monitoring] )
        || ( [Problem Count] >= 0
        && [Problem Count] <= 5 )
        || ( Risk[Risk Count] >= 0
        && Risk[Risk Count] <= 5 )
        || ( VW_OUTAGE[Outagecount] >= 0
        && VW_OUTAGE[Outagecount] <= 5 ), "Green",
    ( Ticket[Incident Count] >= [Average Incidents Logged]
        && Ticket[Incident Count] < [Average+20] )
        || ( Ticket[Monitoring Incident] >= [Average Incidents Logged Monitoring]
        && Ticket[Monitoring Incident] < [Average Monitoring+20] )
        || ( [Problem Count] >= 6
        && [Problem Count] <= 10 )
        || ( Risk[Risk Count] >= 6
        && Risk[Risk Count] <= 10 )
        || ( VW_OUTAGE[Outagecount] >= 6
        && VW_OUTAGE[Outagecount] <= 10 ), "Amber",
    ( Ticket[Incident Count] >= [Average+20]
        || Ticket[Monitoring Incident] >= [Average Monitoring+20] )
        || [Problem Count] >= 11
        || Risk[Risk Count] >= 11
        || VW_OUTAGE[Outagecount] >= 11, "Red"
)

If the above one didn't help you, please provide some raw data in your table with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards