How to control colors with DAX Expressions in Power BI

Introduction

A few weeks ago, I published an article about Information Design:

Three simple rules for information design

As a recap:

IBCS is a company that created a set of rules for Information design.

IBCS helps us use its rules to improve our reporting.

I condensed the original 8 SUCCESS rules into three easy-to-use rules:

1. What is your message?
2. Use a consistent notation.
3. Remove unnecessary stuff.

One important rule in Information Design is to avoid unnecessary colors in our reports.

For example:

When you want to show a time series of data, you want to show it like the following figure:

As you can see, there are three different fill colors for each period:

1. The actual period is black
2. All past periods are grey
3. All future periods have diagonal stripes

The reason for this is to make it easy for the user to spot the actual numbers and to be quickly able to distinguish the past from the forecast data.

Unfortunately, we cannot use diagonal stripes for the column Visual in Power BI.

But we can use Dax to detect the period and to select the color depending on the position of the column relative to the actual period.

Preparations

To be able to implement the solution shown below, you need a Date table with two additional columns:

• YearIndex
• MonthIndex

I mentioned these columns in one of my first Articles about Power Bi and DAX:

3 Ways to Improve your reporting with an expanded date table

This column counts the number of periods starting from the actual date.

The actual date (Now() )is evaluated during the data refresh in the report.

You can use the following expressions in your DAX date table to add these columns:

• "MonthIndex", DATEDIFF(EOMONTH(Now(), 0), EOMONTH([Date], 0), MONTH)
• "YearIndex", YEAR([Date]) – YEAR(Now())

If you use a date table stored in your source system, you can add these columns there.

In SQL Server, you can use the following Statement to set the values for these columns (The Variable @Today contains the date to execution of the Statement à GETDATE() ):

UPDATE [dbo].[Date]
SET [YearIndex] = DATEDIFF(yyyy, @Today, [Date]);

UPDATE [dbo].[Date]
SET [MonthIndex] = DATEDIFF(mm, @Today, [Date]);

Important: The data for the following examples have a Date table with the Actual Date set to March 9th, 2012.

Colors in Power BI

We can use color names in DAX expressions in two different ways:

1. We hover the mouse over the color palette in Power BI and write the color name. For example:

a. Black

b. Grey

c. LightGrey

d. Red

e. Blue

2. Or we can click on "More colors …", select a color and write down the Hexadecimal value for the color. For example:

a. #000000 → Black

b. #FFFFFF → White

c. #DCDCDC → Light Grey

d. #FF0000 → Red

e. #0000FF → Blue

With this information, we can create DAX Measures, which return either a color name or a color code.

Proof-of-Concept

Now, we want to see how it will work.

We create a column chart with one Measure and the Calendar hierarchy assigned to the X-Axis.

As the default color, we see Blue for our columns. But we want to change this to Grey.

The first step is to create a basic Measure with a fixed color:

Dynamic Color PoC = "Grey"

Then, we assign this Measure as an expression to the column:

The result looks like this:

If we change the Measure to the following:

Dynamic Color PoC = "Red"

We get the following result:

Now, we know how to assign a color to a column chart.

We can go to the next step and assign dynamic colors to the years.

Control yearly colors

I will go through each step to build the solution. This way, it’s easy for you, to understand why the solution works at all.

The first step is to create a Measure to see how we can use the YearIndex column from the Date table:

Dynamic Colors =
VAR ActualYearIndex = SELECTEDVALUE('Date'[YearIndex])

RETURN
   ActualYearIndex

To check the result, we add a Matrix Visual to our report with the date hierarchy and the two Measures (The Measure with the value to show and the new Dynamic Color Measure).

The result, shown in a Matrix, looks like this:

As you can see, the Year 2021 has a 0 as YearIndex, and the previous year has a negative number. The following year has a positive number.

The second step is to use this result to set a color according to the YearIndex.

To achieve this, we expand the Dynamic Color Measure to the following expression:

Dynamic Color =
VAR ActualYearIndex = SELECTEDVALUE('Date'[YearIndex])
VAR YearColor = 
    SWITCH(TRUE
          ,ActualYearIndex < 0, "Grey"
          ,ActualYearIndex = 0, "Black"
          ,ActualYearIndex > 0, "LightGrey"
          )

RETURN
    YearColor

As you can see, we use the SWITCH() function to assign a color for each YearIndex value.

The result in the Matrix looks like this:

The result in the column chart looks like this:

This is an (almost) perfect starting point.

But what happens when we Drill down to the month level?

In this case, the colors will inherit the same color as the Year.

But for 2021, we will see the same color scheme at the Month level.

We want to see black for March, Grey for the months before March, and Light Grey for all the months after March.

Add Months

To add the same Dynamic logic to the Month level, we can expand the existing logic to write the following Measure:

Dynamic Color =
    VAR ActualYearIndex = SELECTEDVALUE('Date'[YearIndex])
    VAR ActualMonthIndex = 
        IF( HASONEVALUE('Date'[MonthIndex])
                        ,SELECTEDVALUE('Date'[MonthIndex])
                        ,99
                        )
VAR YearColor =
    SWITCH(TRUE
            ,ActualYearIndex < 0, "Grey"
            ,ActualYearIndex = 0, "Black"
            ,ActualYearIndex > 0, "LightGrey"
            )

VAR MonthColor =
    SWITCH(TRUE
            ,ActualMonthIndex < 0, "Grey"
            ,ActualMonthIndex = 0, "Black"
            ,ActualMonthIndex > 0, "LightGrey"
            )

RETURN
    IF(ActualMonthIndex = 99
        ,YearColor
        ,MonthColor)

The key here is how we evaluate the ActualMonthIndex.

We use the HASONEVALUE() function to check if the current Filter Context contains only one Month or more than one.

On the Year level, this function will return FALSE, as more than one Value is contained in the actual Filter context.

On the Month level, the Filter Context contains only one value. Thus, the function will return TRUE.

The remaining Measure uses the same logic but twice.

The column chart at the monthly level looks like this:

Of course, you can use colors other than black and shades of grey.

But consider that there are users who cannot distinguish that many colors. For example, look at the following figure:

Here I added two rectangles in red and green. Then I copied these rectangles and converted them into a black-and-white picture.

As you can see, the difference between the two grey rectangles is no longer that big.

You must consider such effects when selecting colors for your reports.

Conclusion

Information Design is an essential factor when designing reports.

We need to avoid falling into the trap of using too many colors in our reports.

Power BI seduces us to use too many colors, but we need to know what we are doing.

For example, the Article Use report themes in Power BI Desktop – Power BI | Microsoft Learn teaches you how to use Themes in Power BI.

But even the colorblind theme uses a lot of different colors.

Then, it gets even worse when you look at the downloadable custom Themes.

Not to mention the YouTube videos, like this one:

He started quite well, but then he took a completely wrong direction with the use of that many colors.

When we convert the final report in this video to Black and White, the result is very dark, even though the contrast of the data is still OK. But the differences between the bars aren’t easy to spot.

Remember that the typical business user doesn’t care about the colors, as long as the data is accurate and easy to consume.

References

IBCS – International Business Communication Standards – Web site for the IBCS rules with a readable online version of the rulebook.

I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described here.

I enlarged the dataset to make the DAX engine work harder. The Online Sales table contains 71 million rows (instead of 12.6 million rows), and the Retail Sales table contains 18.5 million rows (instead of 3.4 million rows).

If you appreciate my work, feel free to support me through

Salvatore Cagliari

Or scan this QR Code:

Any support is greatly appreciated.

Thank you.