Should you use Measures for Conditional Formatting?

Streamline Your Power BI Conditional Formatting with Measures

I've always found the built-in conditional formatting in Power BI to be quite confusing. But check this out—you can use a measure for conditional formatting!

Wouldn't it be nice if you could change the color of all your visuals in one go? Well, you can!

Using Measures for Conditional Formatting

Here's how:

1. Set Up Base Measures: Start by creating two base measures:

Color Red = "#df64550"

Color Green = "#36843a"

Finding those #Hex Codes: To find hex codes, you can go to View > Customize Current Theme in Power BI. Alternatively, I use Color Picker from PowerToys, a free Microsoft product. Just press Windows + Shift + C to pick colors from anywhere on your screen.

Create Conditional Formatting Rules:

Next, you’ll set up a conditional formatting rule.

Define the Rule:

• For a measure evaluating variance, such as "Usage vs. Prior Year," you can set the rule as follows:
• If the value is greater than zero, apply the red color.
• If the value is less than zero, apply the green color.

cf rule Usage PY Variance = 
VAR _Measure = [Usage v Prior Year]
VAR _Result=
SWITCH(
    TRUE(),
    _Measure >0, [ColourRed],
    _Measure <0, [ColourGreen],    
    BLANK()
   )  
RETURN
_Result        

You can adjust these conditions based on your specific needs.

Conditional Formatting in Visuals:

• Select the visual you want to format.

Go to the formatting pane and find the appropriate option (e.g., Callout Value for cards, Background Color for tables).

Clear any existing formatting and click on the FX button to set new formatting.

Choose the Field Value option and select the measure you created for conditional formatting

Final Thoughts

Are you already using this technique? Do you find it useful? Share any tips or pitfalls you've encountered in the comments.

Connect with me

https://www.dhirubhai.net/in/wynhopkins/

https://wyn.bio.link/

The AA Team: Training and Consulting: https://accessanalytic.com.au/