In this writing, I want to share in detail how I create a measure in order to show conditionally format matrix/table visualization in Power BI. The measure below is for providing a solution for the question from the Microsoft Power BI Community Forums link down below.
The dataset and visualizations look like below.
From the above figure, when Australia is selected from Visaul 1 (Column chart), the red flag is shown in Visual 2 only for Math subject, because the average mark is lower than the threshold.
In order to make conditional formatting work, first, select Visual 2, and then under Visualizations pane, click the down-arrow beside "Marks avg. measure:" and select Conditional formatting -> Icons.
Select "Format style" = "Rules", and select "What field should we base this on?" = "Highlight measure:"
The condition is, if the result of the measure (Highlight measure:) is 1, then show red flag.
In order to meet the condition, the measure is written like the below. The comment for each row or each function is written in red color.
Highlight measure: = IF ( HASONEVALUE( Sheet2[Country] ), //if only one country is selected from Visual 1 IF ( [Marks Avg. measure:] < MAXX ( FILTER ( PassignScore, PassignScore[Country] = MAX ( Sheet2[Country] ) && PassignScore[Subjects] = MAX ( Sheet2[Subject] ) ), //PassignScore table is not connected and it is filtered by the same country and the same subject that is selected from Visaual 1 and that meets the filter context from Visual 2 PassignScore[PassingGrade] //and then, the only one grade, which is the threshold, is shown (maxx, minx, averagex do not matter because only one number is selected and shown. ), 1 ) )
If you want to highlight the background inside the table visualization, instead of selecting Icon, you can select "Background color". The measure can be written like below.
Highlight measure: = IF ( HASONEVALUE( Sheet2[Country] ), IF ( [Marks Avg. measure:] < MAXX ( FILTER ( PassignScore, PassignScore[Country] = MAX ( Sheet2[Country] ) && PassignScore[Subjects] = MAX ( Sheet2[Subject] ) ), PassignScore[PassingGrade] ), "Red" //Instead of writing Red, you can also input HEX code of the color you want. ) )
Comments