DAX and Conditional Formatting Better Together: Find The Biggest and Smallest Numbers in the Column

In the previous article, I explained the importance of Conditional formatting and mentioned that having a table with no color coding is not a proper way of giving information to your users. In this article, as the second step; I’m going to show you how to use DAX combined with conditional formatting to only highlight the biggest and the smallest number in a column in a table. If you have used the conditional formatting, you are aware that you cannot do the formatting just by saying give me the highest number, or the two top highest numbers. In the Power BI world, DAX is your friend, so let’s see how DAX in combination with conditional formatting can do that for you. If you like to learn more about Power BI, read Power BI from Rookie to Rock Star book.

Prerequisite

The dataset for this model is the random set of numbers I have created, which you can download from here.

Also; to understand the importance of conditional formatting, I recommend you to read part one of this article here.

Our sample database table is as below;

Using RANKX to find the biggest and smallest numbers

RANKX is a function that can be used in many scenarios when you are calculating the rank of a value based on a category. Imagine that we have a table visual like the below example:

Our first step is to create a measure that can give us the rank of values based on customers. And using RANKX, we can write a measure like below;

Rank of Revenue = RANKX(
          ALL('By Customer'),
          CALCULATE(
              SUM('By Customer'[Revenue])
          )
)

“By Customer” is our table name, and Revenue is the column that we want to find out the ranking of it. To learn more about RANKX, read this article.

Now you can add that measure in the table visual, and you will see this output

Using Switch to Choose Color

Now that we know what is the biggest number and what is the smallest number (using the result of RANKX expression), we can set the color based on it. The SWITCH is a function which works like multiple IF THEN ELSE statements. we can write another measure as below;

Background Color = SWITCH(
    [Rank of Revenue],
    1,"Green",
    25,"Red",
    "White"
)

If we want to translate the code above to if-then-else, it means: If the rank of revenue is 1, the color is green, if it is 25, the color is red, and otherwise for any other values it is white. This is the measure that we will be using for coloring the values in the table.

Set Conditional Formatting for Background Color

Now that we have everything ready, we can do the conditional formatting on the table. Select the table visual first, then go to the format tab, and under conditional formatting, select Revenue as the column.

Set the Background color to On, and you will see conditional colors appears immediately. However, this is not the coloring we want. so click on Advanced Controls.

In the advanced control, set the Format By to Field Value, and then select the Background Color measure that we created in the previous step here. then click on OK.

Note that if you are creating your own measures; The measure that you use here, should be producing color values as the output.

After clicking on OK, you will see that there will be only two values color coded, the biggest one as Green, and the smallest one as Red.

Done! You got the very first and the very last number in the ranking now color coded. How simple it is when DAX and Conditional Formatting works with each other! Now, let’s talk about some enhancements.

Font Color: A bit more enhancement

As you can see in the above screenshot, the text value of the revenue is not that much readable because the colors are rather dark. Best would be also creating another measure for Font Color as below;

Font Color = SWITCH(
    [Rank of Revenue],
    1,"White",
    25,"White",
    "Black"
)

Adding that to the table, you can see how it works:

Now, let’s do the conditional formatting this time for Font Color;

In the advanced controls window; Choose the Fixed Value, and then Font Color;

Here is now the final output:

More Enhancements: Color Coding top 3 and bottom 3 values

Well, before you ask for this, I thought better to show you straight away! How if we want to color code not only the top and bottom 1 numbers but also the top/bottom three numbers? Well, that is easily possible, you just need to change your measure expression for the background and font color as below;

Background Color Three = SWITCH(
    [Rank of Revenue],
    1,"Green",
    2,"Green",
    3,"Green",
    23,"Red",
    24,"Red",
    25,"Red",
    "White"
)

It is not a good way of writing what we are after. You need to use Switch, but use it for multiple values, here is a better way of doing it:

Background Color Three = SWITCH(
    TRUE(),
    [Rank of Revenue]<=3,"Green",
    [Rank of Revenue]>=22,"Red",
    "White"
)

same for the Font Color;

Font Color Three = SWITCH(
    TRUE(),
    [Rank of Revenue]<=3,"White",
    [Rank of Revenue]>=22,"White",
    "Black"
)

and then if we use these two new measures (Background Color Three, and Font Color Three) for conditional formatting, this is what we get;

What If Parameters for Conditional Formatting: The sky is the limit!

When it comes to combining DAX and visualization, then the sky is the limit of what you can do. Let’s say you don’t know is it the top three that you want to color code, or four, or five. and also you don’t know is it different from the bottom bound of the values. So, as a solution, you can use What If parameters in Power BI to create two parameters; one for the upper bound, and one for the lower bound.

Start by creating a new what if parameter under the modeling tab. name it as Upper Bound, and then minimum as 1, maximum as 10, default as 5, and increment 1 each time. make sure the Add slicer to this page is selected.

Do it one more time for the lower bound with the configuration below;

Now you should have two slicers in your page for each of the what if parameters.

Let’s use these two in our background color and font color measures. This is what the updated background color measure looks like:

Background Color Parameter = SWITCH(
    TRUE(),
    [Rank of Revenue]<='Upper Bound'[Upper Bound Value],"Green",
    [Rank of Revenue]>=
        CALCULATE(
            COUNT('By Customer'[Customer]),
            ALL('By Customer')
            )+1-'Lower Bound'[Lower Bound Value],"Red",
    "White"
)

Well, the DAX expression is a bit more complicated than what you expected! Let’s explain a bit of detail here: ‘Upper Bound'[Upper Bound Value] and ‘Lower Bound'[Lower Bound Value] are the values selected in the slicers of what if parameter tables. Because the Lower Bound value is a value between 1 to 10, and we want this value to be deducted from the maximum rank in the revenue column, so I used a calculate function to count all records in the table, and then use that as the source of deduction. I added one to it, to avoid results such as 25-1=24, we want also 25 to be color-coded, which is 25+1-1.

And for the Font color;

Font Color Parameter = SWITCH(
    TRUE(),
    [Rank of Revenue]<='Upper Bound'[Upper Bound Value],"White",
    [Rank of Revenue]>=
        CALCULATE(
            COUNT('By Customer'[Customer]),
            ALL('By Customer')
            )+1-'Lower Bound'[Lower Bound Value],"White",
    "Black"
)

After conditional formatting this is the result:

It works even for other visuals

The solution you learned in this article, is not just for table visual. you can use it in a bar chart, column chart, and some other visuals. Obviously for these, because the background color white would be a bit invisible, I changed the measure as below;

Background Color Parameter Chart = SWITCH(
    TRUE(),
    [Rank of Revenue]<='Upper Bound'[Upper Bound Value],"Green",
    [Rank of Revenue]>=
        CALCULATE(
            COUNT('By Customer'[Customer]),
            ALL('By Customer')
            )+1-'Lower Bound'[Lower Bound Value],"Red",
    "Orange"
)

The above measure replaced the White color with Orange.

And here is the output now:

All in one as a wrap up looks like this:

Summary

Multiple techniques used in this article to achieve something which I believe every business would need in their visualization; conditional formatting based on top/bottom values. We used RANKX function in DAX to calculate the rank of values and then using SWITCH we produced the output of coloring. Then using conditional formatting with the fixed value we put it in the visualization. You also learned how the process can be enhanced using parameters as a more dynamic approach. Hope you liked it and use it in your implementations 🙂

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.