Conditional Formatting – Moving to the Next Level

Last week, I went through the basics of conditional formatting. I shared how it helps take your reports to the next level and the steps required to use it. We looked at gradient and rule based conditional formatting but did not touch the field option. I wanted to split this one out because it can be a little more complicated but is quite versatile.

What is Field Based Conditional Formatting?

Isn’t all conditional formatting based upon fields? Of course it is! We are always looking at field values to activate conditional formatting. Field based conditional formatting is not looking at the field values for what needs to happen. Instead, it is looking to the field for what formatting is supposed to appear.

Let’s use our example from last week where we looked at revenue versus goals. We used a simple set of rules to highlight table rows as red, yellow, or green based on values.

Instead of setting a bunch of rules, we can create a measure that specifies these colors. If you remember a few weeks ago, I did a post on using the SWITCH() function in DAX. I am going to use it to create a measure to specify the colors I want to use.

Field Formatting v1 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "Red",
        SumRevenue < 1500000, "Yellow",
        SumRevenue >= 1500000, "Green",
        "Gray")

Note that I am using colors by name in my measure. It is not fancy, but it gets the job done. Once the measure is ready, we just need to apply it to the column. I select field as my format type and then select the measure I just created.

The result is my table formatted as expected.

And just like that we are using a measure to drive our conditional formatting.

This Looks Great! Can I Customize My Colors?

You can absolutely customize your colors! Instead of writing out the words, you can use hex codes to set the color.

Field Formatting v2 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "#9c0a00",
        SumRevenue < 1500000, "#f7da00",
        SumRevenue >= 1500000, "#006616",
        "Gray")

Using a branding guide to help me, I pulled the red, yellow, and green hex codes to use in my report.

But now my colors are a little too dark How do I fix that? Here’s a little pro tip – you can add two numbers on the end of your hex code to determine the transparency of the color. I wanted to soften the colors a bit and make it 50% transparent, so I added a 50 to the end of each hex code.

Field Formatting v3 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "#9c0a0050",
        SumRevenue < 1500000, "#f7da0050",
        SumRevenue >= 1500000, "#00661650",
        "Gray")

And the difference is dramatic!

This makes a huge difference in our formatting and it is much easier to read.

Why Go Through All Of This Effort For Conditional Formatting?

If you are anything like me, you are probably wondering why you should go through this kind of effort to build out measures for conditional formatting. Every tool has a purpose and this is one that rarely gets leveraged. But consider some of these use cases where you might find it useful.

Complex Rules

When using rules, you find that you are put into a simple set of conditions that drive your formatting. Maybe you have a complex set of conditions that you need to consider. Instead of a simple greater than, less than, or between set of rules, you can build out some complex logic.

For example, you only want the colors to appear for the months in Q4. You cannot accomplish this with the standard set of rules. But with a little extra DAX, you can make that happen.

Field Formatting v4 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(QUARTER(MAX(Revenue[Month])) = 4,
    TRUE(), 
        SWITCH(TRUE(),
            SumRevenue < 1450000, "#9c0a0050",
            SumRevenue < 1500000, "#f7da0050",
            SumRevenue >= 1500000, "#00661650",
            "Gray")
    )

If you wanted to get really crazy, you can build out a complex measure that darkens the color the closer you get to the due date of a goal. The sky is the limit!

Color Flexibility

What if you had a report that can be filtered for multiple customers? You could setup your formatting to highlight values using their primary color color scheme.

If you have a customer table, you can specify some key palette colors and reference them in your SWITCH() statement. This enables a personal touch for each customer without having to create separate report pages or files for each one. Talk about scaling out your reporting!

Anything Else I Need To Know?

Remember, you are building out your conditional formatting using measures. You always want to optimize them to be efficient and not tax your report canvas. You are adding additional calculations to your report canvas, so don’t overdue it!

So how about you? Have you used field based conditional formatting before? If not, do you have a use case? If so, tell me about it in the comments below!