Conditional formatting a matrix with Calculation groups in Power BI

by

Time to read: 5 minutes

Calculation groups is a powerful feature in Power BI. However, using them adds some complexity and brings some limitations to Power BI’s native functionality. Or should I say, changes them.

One of these cases are related to the use of Conditional formatting using a calculation group as e.g. columns in a Matrix visual.

In this post I will show you a solution on how you can add conditional formatting to a single column in a Matrix visual in Power BI. The result will look like below.
Conditional formatting is applied on the (only) measure used in the values section.

Showing conditional formatting being applied in a Power BI Matrix visual
End result in Power BI

Adventure works DW is used for the demo. Lets start to go through all steps to replicate the solution.

Measures

Sales mount, which sums the SalesAmount of FactInternetSales

Sales amount :=
SUM ( FactInternetSales[SalesAmount] )

CF, which will be used in the conditinal formatting rules.
This measure will return 1 if Sales amount is above 300 000. Which is the limit where I want the text to turn green.

CF :=
IF (
    [Sales amount] > 300000,
    1
)

Conditional formatting

I set up conditional formatting using Format by “Rules”. The rules will be based on the measure CF, which is seen above.

Only one rule is added in this case. If the output value from CF is 1, then format the text color as green.
In any other case keep the formatting as is.

Showing conditional formating rules in Power BI
Conditional formatting based on measure CF

Calculation groups

I am using a calculation group with two calculation items in this example. The calculation items are named “Actuals” and “Actuals LY”.
“Actuals” returns the value from the selected measure, which in this case is the sum of Sales amount. The other item “Actuals LY” simply adds a time intelligence function to return the last year values.

Showing calculation group "Time intelligence" in Tabular Editor
Calculation group in Tabular Editor

DAX code for calculation items:

Actuals
SELECTEDVALUE()

Actuals LY (Without added logic)
CALCULATE (
    SELECTEDMEASURE (),
    SAMEPERIODLASTYEAR ( 'DimDate'[FullDateAlternateKey] )
)

Actuals LY (With added logic)
IF (
    NOT ISSELECTEDMEASURE ( [CF] ),
    CALCULATE (
        SELECTEDMEASURE (),
        SAMEPERIODLASTYEAR ( 'DimDate'[FullDateAlternateKey] )
    )
)

I have given two example of calculation item “Actuals LY” above. If you would use the first example, named “without added logic”, you would get a result similar to this in Power BI, seen below.

Showing conditional formatting in a Power BI matrix visual. The formatting is applied to all columns.
Conditional formatting without extra logic

The conditional formatting will be applied to all columns (calculation items) visible in e.g. a matrix visual.

This is because we only have one measure in the values section of the visual. The conditional formatting will be applied to the whole measure including all columns.

As you can see in the other example of “Actuals LY“, the CALCULATE() function is wrapped inside an IF () statement. As you might know, logic in a calculation item is applied to all measures by default. The logic in this example is added to prevent the CF measure to be evaluated when in the scope of calculation item “Actuals LY“. It will therefore not return 1 in case the sum is above defined threshold.

You can read more about including and excluding measures from calculation items on sqlbi.com here.
That is an excellent introduction article about calculation groups, which also includes this use case.

Let’s go back to the IF () statement and break it down.
ISSELECTEDMEASURE ( [CF] ) returns TRUE if measure CF is in scope of the current calculation item. I have wrapped this inside the function NOT (), for simplicity reasons. The evaluation of NOT () will return the opposite value of ISSELECTEDMEASURE ().
In this case the ISSELECTEDMEASURE () will return TRUE. The outer NOT () will return FALSE.
This means that calculation item will return blank for measure CF, because the false condition in the IF () function will return blank by default.
In any other case it will return the evaluation of CALCULATE () function, which in this case will return the sales amount for the previous year.

There are other ways of handling this specific case of using conditional formatting on specific columns while using calculation groups. You can for example move the logic to the measure which is used in conditional formatting rules. The example above is only one way of doing it.

Limitations

There are of course some limitations when working with conditional formatting in cases like this. These are the ones I’ve found so far.

  • The solution explained in this post is not applicable to data bars or Web URL conditional formatting. Data bars can’t be formatted based on rules, which is a requirement for this to work. Web URL can’t be formatted based on rules using a measure, which is a requirement for this solution to work.
  • There is a small limitation when adding the solution in this post on a conditional formatting using icons.
    While it does work, it will actually give you a “blank” icon which still reserves space in the column. See below.
Showing conditional formatting in a Power BI matrix visual. The missing icon formatting is replaced with blank space
Blank “icon” using conditional formatting

Thank you for reading!

That all for this time. Did you like this post? Please let me know in the comments below!

You can find a full working .pbix file of this solution here.

Leave a Comment