Conditional formatting based on custom fields: cha…

Scenario:
The default display style of Power BI (whether it is a table or a matrix) is nothing more than interlaced display. Such a layout cannot help us intuitively find the details of each country at a glance. The conditional format in PowerBI can help us solve this kind of problem very well.

This article uses a simple example to briefly introduce how to change the interlaced background style of the table/matrix based on measures and custom columns.

Default display:

 

 

Expected result:
If we can make the background colors of the same country consistent and staggered, we can more intuitively distinguish the sales details of different countries.

 

 

Guideline: 

To achieve this requirement, we have two main steps: 

1. Create a ‘Color Index’ field
2. Set background conditional formatting for all fields in the table visual based on the ‘Color Index’ measure.

 

DAX Method: 

 

1)Create a ‘Color Index’ measure using Dax：
Here we need to implement it in two steps:

1. Rank countries by field value.
2. Use the parity of the ranking metric in the previous step to define the background color of the report field.

 

 

When ranking countries, we need to use function ‘RANKX’, the measure is as follows:

 

Rank country = 
VAR country =
CALCULATETABLE ( VALUES ( financials[Country] ), ALLSELECTED () ) 
// Generate a temporary table for all countries
VAR Currentcountry =
    SELECTEDVALUE ( financials[Country] ) //Get the country name of the current row
VAR result =
    IF (
        HASONEVALUE ( financials[Country] ),
        //COUNTROWS(VALUES(financials[Country])) = 1,
        RANKX (
            country,
            //As a parameter table in Rankx
            financials[Country],
            //expression evaluated for each row of table
            Currentcountry,
            //use current value as Ranking basis
            ASC
        )
    )
RETURN
    result

In this DAX formula , we used  function ’ CALCULATETABLE’ to generate a temporary table for all countries and stored it in the variable ‘country’. We get the country value of the current context through function ‘SELECTEDVALUE’ and store it in the variable ‘Current country’. Finally, the above variables are brought into the corresponding parameters of  function ‘RANKX’ to return the desired ranking.

The function ‘HANSONEVALUE’  is used to judge the total line(Sometimes we can also use this function to change the wrong total line). 

After creating the ranking, we need to divide by 2 and take the remainder to define the parity.
Here we use  the ‘MOD’ function, which is used to return the remainder after a number is divided by a divisor.

Color Index = MOD([Rank country],2)

 

2) Set background conditional formatting for all fields in the table visual based on measure ‘Color Index’

 

Here we need to use conditional formatting to edit the background color of the report fields. There are only two values in ‘color index’, e.g.,0 and 1. So we directly define the colors of maximum and minimum values here (specify two different colors for 0 and 1).

 

 

 

As of now, since conditional format setting is for a single field rather than the entire row, you have no choice but to repeat the above operations for all the fields in the table visual.

 

 

 

Power Query Editor Method: 

 

In Power Query Editor, we can also insert a similar ‘Color Index’ column to the original table:

Specify the ‘Country’ column to group by.

Add an index column, then insert the following ‘Parity’ column based on the index column.

= Table.AddColumn(#"Added Index", "ColorIndex", each Number.Mod([Index], 2), type number)

 

Here we use M function Number. Mod  to divide column value by ‘2’ for remainder operation. You can also use the built-in ‘Modulo’ function to achieve it.

Expand the table to identify all table rows by the ‘Color Index’ column.

Set background conditional formatting for each field in the table

 

 

 

 

Summary: 
The above is how to change default background display style. These are just basic content. We achieved it through the steps of establishing auxiliary measures/custom columns ->defining conditional formatting. Similarly, you can also dynamically define and control fonts, icons, etc. according to your own custom fields to achieve other layout styles.

 

Please refer to the attachment for details. 

 

Author: Eason Fang

Reviewer:  Icey