How to do conditional formatting based on a measure and column from table

[Anonymous]

Hi coaches,

I am new to Power BI and looking forward to your help for the below.

Requirement is to create a Power BI report from the Below screenshot of a PPT slide

Have to replicate same in Power BI

The only tables are highlighted in yellow. Rest all are text boxes.

I created 3 matrix visulaizations:

Have used 2 columns: Model ID, Materiality

“Materiality” has values Level1, Level2, Level3

Created a new measure with expression Count(model ID)

I pulled Materiality in columns section, and Count(model ID) in values section and report looks like shown below

Below are my 2 questions:

1. I am facing issue in creating conditional formatting based on 2 columns i.e. Materiality and new measure - count(model ID)

   I want to create a new calculated column, and then use the new column in conditional formatting of background color of the cells based on below conditions:

   If Materiality = “Level1” and count(model ID)=1 then “Amber”

   If Materiality = “Level1” and count(model ID)<2 then “Green”

   If Materiality = “Level2” and count(model ID)>=5 then “Amber”

   If Materiality = “Level2” and count(model ID)<10 then “Green”

   If Materiality = “Level3” and count(model ID)>=10 then “Amber”

   If Materiality = “Level3” and count(model ID)<20 then “Green”

   Desired result is as shown below. The color code condition is different for each level1,2, 3.

   
   

   After I get the new column with values - Amber, Green, I can use this column for conditional formatting of background color of cells.

   2.  Need to create a table that would show the text “A” if any of the cell in left hand side matrix is yellow. In the above example, Level 1 and Level 3 have yellow background, so the right hand side table should display “A”.

[parry2k · Accepted solution]

@Anonymous try this:

Color Measure = 
VAR __Materiality = SELECTEDVALUE ( Table[Materiality]
RETURN
SWITCH ( 
   __Materiality,
   "Level 1", <<your if condition for level 1>>,
   "Level 2", <<your if condition for level 2>>,
   "Level 3", <<your if condition for level 3>>
)

[Anonymous]

@parry2k It worked. I am so grateful for your help. Couldn't have done without your help.

Kudos!!!

[Anonymous]

Hi All, Please consider this urgent.

I am trying to create a new measure with values "red, amber, green" based on count(model ID) and materiality.

For each materiality, the condition is different.

Can somebody please help me with the below conditional statement:

Measure 3 = CALCULATE(
IF([Measure 2]<=7,"Amber", IF([Measure 2]>=8,"Red","Green")),FILTER('2a','2a'[Materiality]="level1"),
IF([Measure 2]<=10,"Amber", IF([Measure 2]>=20,"Red","Green")),FILTER('2a','2a'[Materiality]="Level2"),
IF([Measure 2]<=5,"Amber", IF([Measure 2]>=1,"Red","Green")),FILTER('2a','2a'[Materiality]="Level3")
)

Getting the error when I try the above

@parry2k @amitchandak @Ritaf1983 @Ahmedx 

[Anonymous]

The sample data looks like this on which report is built.