Forum Discussion
How to do conditional formatting based on a measure and column from table
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:
- 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.
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”.After I get the new column with values - Amber, Green, I can use this column for conditional formatting of background color of cells.
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>> )
4 Replies
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
- parry2k
Super User
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>> )parry2k It worked. I am so grateful for your help. Couldn't have done without your help.
Kudos!!!