cancel
Showing results for 
Search instead for 
Did you mean: 

Conditional formatting based on custom fields: change the default background style

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:

V-lianl-msft_0-1612948847786.png

 

 

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.

V-lianl-msft_1-1612948847791.png

 

 

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.

 

V-lianl-msft_2-1612948847797.png

 


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)

eason1.png

 

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).

 V-lianl-msft_3-1612948847799.png

 

 

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.
V-lianl-msft_4-1612948847807.png
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.
V-lianl-msft_5-1612948847809.png
Expand the table to identify all table rows by the ‘Color Index’ column.
V-lianl-msft_6-1612948847811.png


Set background conditional formatting for each field in the table

 

V-lianl-msft_7-1612948847817.png

 

 

 

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