Skip to main content
HomeTutorialsPower BI

Complete Guide to Power BI Conditional Formatting

Learn how to use conditional formatting to boost your Power BI reports.
Mar 2024  · 7 min read

Conditional formatting enhances your visualizations and allows you to draw attention to critical metrics or red flags, making it easier to analyze and understand your data at a glance.

This tutorial will explore conditional formatting from basic to advanced applications. Plus, discover the biggest pitfalls when applying conditional formatting in Power BI and how you can avoid them in your reports.

Taking our Data Analyst in Power BI career track is a surefire way to learn everything you need to know to get certified and land a job.

Remember to download our Power BI cheat sheet and keep it on hand as you learn more about Power BI and conditional formatting.

Power BI Cheat Sheet

DataCamp Power BI Cheat Sheet

Understanding Conditional Formatting in Power BI

In Power BI, you can apply conditional formatting in many different ways, such as dynamically formatting individual elements in your visuals or changing the font color of data labels and chart titles.

Power BI offers an easy way of applying basic conditional formatting to change the background and font color of cells in a table or matrix. You can also add data bars (which fill the cell with a bar proportional to the value it represents) and icons (such as arrows or shapes) based on the values in the cells of a table or matrix.

The conditions for formatting can be simple (e.g., values above or below a certain threshold) or complex, involving DAX expressions with highly specific formatting rules.

Power BI evaluates each data point against the specified conditions and applies the formatting styles automatically whenever the underlying data changes.

Basic Conditional Formatting in Power BI

These are the main types of conditional formatting you can apply to the table or matrix visuals in Power BI:

  • Background color and font color
  • Data bars
  • Icons
  • Web URLs

Basic Conditional Formatting in Power BI

Image by author

Background color and font color

These options allow you to change the background color or font color of cells or text based on their values. Background color formatting is commonly used to create heat maps within tables or matrices, making it easier to spot high and low values through color gradients.

There are three format style options here. You can apply conditional formatting based on a field value, a gradient, or rules.

Format style options

Image by author

Field value

Using this format style, you can apply colors based on the cell values or other fields in your data model. This option is more complex since it requires colors to be specified in a column or a DAX measure to work correctly.

We provide an example of how to do this in the next section.

Gradient

The gradient format style applies a color scale across the range of values, often from a low-value color through a midpoint (optionally) to a high-value color.

Power BI gradient format style

Image by author

Rules

Rules-based formatting lets you set specific conditions (e.g., greater than, less than, or equal to a specific value) for when to apply certain background colors or font colors. This method offers more control than the gradient and is good for comparing values against a target or predefined threshold.

You need to specify each rule individually with a color that should be applied when those rules are met. Remember to apply each rule in the correct order, especially if your rules overlap.

Power BI Rules formatting

Image by author

Here is an example of how the above rule would look in a table:

Power BI Rule example

Image by author

Data bars

Data bars add a horizontal bar within the cell. The length of the bar represents the cell's value compared to other values in the column. This is a useful way of visually gauging and comparing data points directly within the cell.

Power BI data bars formatting

Image by author

Here is an example of how the bars look in a table:

Power BI data bars formatting example

Image by author

Icons

Icon sets are applied the same way we did using the rules format style above, except you can add symbols or icons next to your data points based on the defined rules.

For example, you could use upward or downward arrows to indicate performance trends or, as in our example below, shapes to indicate whether certain targets have been met.

Power BI icons formatting

Image by author

Here is an example of how the above icon rules look in our table:

Power BI data icons formatting example

Image by author

Web URLs

Although not a visual style per se, this type of conditional formatting allows you to add hyperlinks to your tables based on the URL data in another field. This can be useful for creating drill-through experiences or linking to external resources for additional context.

Power BI Conditional Formatting Based on DAX Measures

As we saw in the previous section, applying rule-based conditional formatting to your charts is quick and easy with the built-in Power BI formatting menu.

However, what if you have several similar visuals, like cards, for displaying different metrics?

You would have to go through the same process for applying red-yellow-green rules to each card in your report. This is a tedious and highly manual process. Even worse, what if you just need to change the value for when the font color of each card turns yellow?

Instead, you can create DAX measures to define the conditions for when the colors should be applied. Not only that, but you can easily change a single DAX measure, and every visual that uses it for conditional formatting will be updated automatically.

Let's review a simple example using the SWITCH function. If you're unfamiliar with the function or how it works, check out our comprehensive SWITCH tutorial.

First, we create our measure, which assigns colors based on the difference between our profit margin and the target.

Profit Margin vs Target Color = 
VAR Diff = [Profit Margin] - [Profit Margin Target]
RETURN
SWITCH (
    TRUE(),
    Diff > 0, "Green",
    Diff < 0, "Red",
    "Black" )

After creating a bar chart showing profit margin by product, we can go to the formatting pane for our visual. Under the “Columns” dropdown, we can select the “fx” icon to add conditional formatting to the colors of the bars.

Power BI conditional formatting

Image by author

Like with the background color and font color conditional formatting options, we can choose between gradient, rules, or field value. For this example, we chose field value and added our new measure.

field value

Image by author

Now, the colors of the columns in our bar chart will change depending on whether the profit margin target was reached.

Columns in the bar chart with different colors

Image by author

Advanced Conditional Formatting Based On User Selection

Starting with a bar chart showing the total sales by month, we want to highlight the top-performing months so that they stand out to our users. First, we’ll apply basic conditional formatting based on the top three sales months. Then, we use an advanced technique to make the conditional formatting more dynamic.

Total sales by month chart

Image by author

Top 3 sales months

For the first example, we create a measure that ranks the total monthly sales and highlights the top 3 in green while the other bars are gray.

Top 3 Monthly Sales = 
VAR Ranking = RANKX ( ALL(Financials[Date]), [Total Sales], [Total Sales], DESC )
VAR Top3 = IF ( Ranking <= 3, "Green", "Gray" )
RETURN Top3

To improve the readability of our highlighted bars, we also create a measure for the font color of the data labels. In this case, the top three green highlighted bars will have a black font color while the other bars will have a white font color, essentially hiding those data labels from the bar chart.

Top 3 Monthly Sales Data Label = 
IF ( [Top 3 Monthly Sales] = "Green", "Black", "White" )

Below is the result of applying this formatting, which is already looking much more striking:

Total sales by month chart highlight

Image by author

Top N sales months based on user selection

In this second example, we take our conditional formatting a step further by allowing the user to select the number of months they would like to highlight in the bar chart.

We can achieve this by using parameters based on a numeric range. Go to the “Modeling” tab in the Ribbon, select “New parameter,” and then “Numeric range” from the drop-down menu.

Top N sales

Image by author

The parameters dialog box asks us to enter a name for our parameter and the minimum, maximum, and desired increment. For this example, we set 5 as the maximum.

Top N sales parameters

Image by author

Once we create the parameter, a new table gets added to our data model.

Top N sales data model

Image by author

Now, we can adjust our measure to use the “Top N Value” measure from this new “Top N” table.

Top N Monthly Sales = 
VAR Ranking = RANKX ( ALL(Financials[Date]), [Total Sales], [Total Sales], DESC )
VAR _TopN = IF ( Ranking <= 'Top N'[Top N Value], "Green", "Grey" )
RETURN _TopN

We also adjust the measure for the font color of the data label so that it dynamically changes based on the number of highlighted columns.

Top N Monthly Sales Data Label = 
IF ( [Top N Monthly Sales] = "Green", "Black", "White" )

The result is a dynamically formatted bar chart that the user can adjust as needed:

dynamically formatted bar chart that the user can adjust as needed

Image by author

Tips for Applying Conditional Formatting in Power BI

1. Start with a clear objective

Understand what you want to achieve with conditional formatting. For example, if you want to highlight specific outliers in your data, there might be better choices than icon sets. Having a clear objective with your report will guide your formatting choices.

2. Use color with purpose

Choose intuitive or meaningful colors for your users (e.g., red for decrease, green for increase) to make your visuals instantly understandable.

Also, consider whether your users may be color blind and improve accessibility in your reports by using color schemes that are distinguishable to all users.

3. Simplify your color palette

Use a simple and consistent color palette to avoid overwhelming your audience. Too many colors can be distracting and may reduce the impact of your dashboard.

Reserve strong or contrasting colors for the most important data points you want to highlight.

4. Test regularly

Review and test your report regularly with end-users to ensure the conditional formatting is clear, intuitive, and adds value. Getting feedback from your users can go a long way toward ensuring your formatting is clear and easy to understand.

Also, be sure to keep an eye on the performance of your Power BI reports, especially if you use complex DAX expressions for conditional formatting, as they can impact report loading times.

5. Iterate and improve

Conditional formatting in Power BI is not a "set it and forget it" task. As your data changes or as you receive feedback from your users, be prepared to iterate and improve your formatting choices to keep your reports valuable and aligned with the original objectives of the report.

Avoid the Biggest Pitfall of Conditional Formatting in Power BI

Conditional formatting overload.

Find a balance between highlighting key points and overloading your report with so many colors that they lose all their meaning.

Be selective with what you choose to highlight. Too much conditional formatting can make a report busy and hard to read. Focus on key metrics and insights that drive decision-making and tie in with the objective of the report or dashboard.

Use icons and data bars to add context or to simplify the understanding of the data without adding additional columns, charts, or other text. Arrows can indicate trends, while data bars can give a quick comparative view of a column in a table.

Conclusion

Using conditional formatting in Power BI, you can create eye-catching reports, but it should be applied carefully and with a clear objective in mind.

We covered several ways of applying conditional formatting, from basic to advanced. By following our tips, you can create visually appealing reports that are easy for your users to understand and interpret.

Check out our course on data visualization in Power BI to learn more about effectively visualizing and presenting your insights to your users.

FAQs

Can you copy conditional formatting rules in Power BI?

Yes, you can do this by selecting the visual with the conditional formatting that you want to copy. Then click the Format Painter icon under the Home tab and click on the visual you want to apply the conditional formatting to.

What should you do if conditional formatting is not working?

If conditional formatting is not working, there could be several reasons, such as issues with the data, the rules not being set up correctly, or compatibility problems. You can do some troubleshooting by checking the data types, the DAX measures, and the conditional formatting rules (including the order of the rules).

Can you use custom icons in conditional formatting?

Power BI provides a few default icon styles that can be used in conditional formatting, but you can also add custom icons by importing them into Power BI as a theme and then selecting them in the conditional formatting window for icon sets.


Photo of Joleen Bothma
Author
Joleen Bothma
Topics

Continue Your Power BI Learning Journey!

Course

Introduction to Power BI

3 hr
338K
Master the Power BI basics and learn to use the data visualization software to build impactful reports.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

Top 32 AWS Interview Questions and Answers For 2024

A complete guide to exploring the basic, intermediate, and advanced AWS interview questions, along with questions based on real-world situations. It covers all the areas, ensuring a well-rounded preparation strategy.
Zoumana Keita 's photo

Zoumana Keita

15 min

Top 10 Business Intelligence Conferences in 2024

An overview of the top 10 international business intelligence conferences planned for 2024, including their dates, participation fees, formats, hosting cities, main focus, key discussion topics, and target audience.
Elena Kosourova's photo

Elena Kosourova

5 min

Becoming Remarkable with Guy Kawasaki, Author and Chief Evangelist at Canva

Richie and Guy explore the concept of being remarkable, growth, grit and grace, the importance of experiential learning, imposter syndrome, finding your passion, how to network and find remarkable people, measuring success through benevolent impact and much more. 
Richie Cotton's photo

Richie Cotton

55 min

Mastering SWITCH in DAX for Power BI: A Comprehensive Guide

Explore the Power BI SWITCH function, what it is, how to use it, and some common pitfalls and best practices.
Joleen Bothma's photo

Joleen Bothma

10 min

A Step-By-Step Guide to Visualizing KPIs in Power BI

Learn how to effectively visualize KPIs in Power BI using visuals and DAX measures.
Joleen Bothma's photo

Joleen Bothma

8 min

Power BI Slicer Tutorial: A Comprehensive Guide to Enhancing Your Reports

Unlock the power of Power BI slicers with our step-by-step tutorial. Learn to create, customize, and optimize slicers for better reports.
Joleen Bothma's photo

Joleen Bothma

7 min

See MoreSee More