BI Conditional Formatting in Power BI: Rules and Uses

BI conditional formatting uses color, icons, data bars, or other visual cues to make important values in a business intelligence report easier to spot. In Power BI, select a supported visual, open Format visual, and configure a conditional formatting option such as background color, font color, or data bars; the exact controls depend on the visual and the Power BI interface version.

What BI conditional formatting means

BI stands for business intelligence: the collection and presentation of data to help people understand performance and make decisions. Conditional formatting changes a visual element according to data or a rule. A low stock count might appear red, a strong sales result green, and a middle result amber. Instead of making readers compare every number manually, the formatting gives them a visual signal about which values deserve attention.

Conditional formatting is not the same as applying one fixed color to a whole chart or table. A fixed format is identical regardless of the underlying value; a conditional format responds to the data, a threshold, or a measure. It can be used to emphasize exceptions, show magnitude, or communicate categories, but it does not change the data itself or explain why a result occurred.

Power BI is a common meaning of “BI” in this context, although other reporting products have their own conditional-formatting controls. The guidance below focuses on Power BI Desktop and its standard table, matrix, and chart visuals. Exact pane labels can change as Microsoft updates the interface, and some custom visuals offer different options.

How it works in Power BI

A conditional format connects a visual property—such as a cell’s background color—to a field or measure. Power BI evaluates the value in the context of the visual, including filters, slicers, and the row or column being displayed. The resulting color or symbol can therefore change when a reader filters the report.

For example, a table of monthly sales could color each sales cell based on its value. A rule might use red below $10,000, amber from $10,000 to under $25,000, and green at $25,000 or above. Those thresholds are illustrative, not standards: they should reflect the business question, the units, and the intended audience.

Power BI generally offers several ways to define the result:

Rules: Set ranges or conditions, such as values below a target.
Color scales: Map low and high values to colors, sometimes with a midpoint, to communicate relative magnitude.
Field value: Use a field or measure that returns a color value, which is useful when the logic is more complex than a simple threshold.
Icons or data bars: Add a symbol or bar that represents a status or the relative size of a value, where the selected visual supports it.

Apply conditional formatting to a table or matrix

For a table or matrix in Power BI Desktop, first add the data you want to display and select the visual. In the current formatting experience, open Format visual, find Cell elements, select the relevant field or series if prompted, and turn on an option such as Background color, Font color, Data bars, or Icons. Use the adjacent fx control to set the conditional formatting method. In some interface versions, similar controls appear under the field’s conditional formatting menu or the visual’s values settings.

In the formatting dialog, choose a method such as rules, a color scale, or field value, then choose the field on which the format should be based. Review the options for blank values, summarization, and color selection where available. Check the result in the visual, not just in the dialog: a rule that looks sensible on one row may be confusing when applied across a matrix with different groups or subtotals.

For a chart, select the chart and inspect Format visual for a Data colors or equivalent color setting with an fx control. Availability depends on the chart type and the data structure. A chart with one series may expose different options from a chart whose legend creates several series. If the relevant control is absent, the visual may not support that kind of conditional formatting.

Choose between rules, scales, and field values

Rules work well when the audience needs clear categories tied to meaningful cutoffs: overdue versus on time, below target versus meeting target, or low versus adequate inventory. Choose non-overlapping boundaries and state the units or target in the report. A threshold such as “less than 10” means something different for dollars, days, and defect rates.

A color scale is useful when the main task is comparing relative values across a range, such as spotting the highest and lowest sales figures in a table. It can be less suitable when a fixed target matters more than the relative position. A value can look “green” because it is high compared with other rows while still falling short of the organization’s goal.

Field-value formatting offers flexibility when a measure needs to return a color. For example, an existing measure named [Revenue] could be used by a DAX color measure. With that measure available in the model, this illustrative measure returns a hex color for each revenue result:

Revenue Status Color =
VAR RevenueValue = [Revenue]
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(RevenueValue), "#BFBFBF",
        RevenueValue < 10000, "#F4CCCC",
        RevenueValue < 25000, "#FFE599",
        "#D9EAD3"
    )

The example assumes revenue is expressed in the same units as the thresholds. It returns gray for a blank, pale red below 10,000, amber from 10,000 to below 25,000, and pale green at 25,000 or more. To use this approach, select Field value in the conditional formatting settings and choose the color measure as the field. DAX measure definitions are not Excel formulas; create the measure in Power BI’s model or report interface. Confirm that the visual evaluates the measure in the context you intend.

Use color and symbols responsibly

Formatting should make a report quicker to interpret, not turn every value into an alert. If red, green, bold text, icons, and bars are all used at once, readers may struggle to tell which signal matters. Reserve strong colors for meaningful exceptions and keep the mapping consistent across pages. If red means “below target” in one visual, avoid using it to mean “highest value” elsewhere without a clear reason.

Do not rely on color alone to convey essential status. Some readers have color-vision differences, and reports may be printed or viewed on screens with different contrast. Pair color with a label, icon, or understandable value where practical. Use sufficient contrast for text, and test the report at the size and display conditions in which people will actually read it.

Also decide whether the purpose is to show absolute performance or relative variation. A color scale recalculated across a filtered set can change its appearance when a slicer changes, even if one underlying value has not changed. If readers need a stable pass/fail judgment, a fixed threshold rule is often clearer than a scale based on the current minimum and maximum.

Common problems and how to troubleshoot them

The formatting option is missing: Check that the selected visual supports the format, that you selected the visual rather than a page element, and that the correct field or series is active. Custom visuals may have their own settings or limitations.
Every row has the same color: Check the field selected under “based on” and how it is summarized. A measure or aggregation that returns one shared result can produce the same format throughout the visual.
Thresholds behave unexpectedly: Verify the value’s units, data type, aggregation, and boundary logic. For example, distinguish “less than 25,000” from “less than or equal to 25,000.” Test values just below, exactly at, and just above each cutoff.
Blank values look like failures: Decide whether blanks mean missing information, zero, or not applicable. Handle them explicitly in the rules or measure rather than allowing readers to infer a status that the data does not support.
Colors change after filtering: Check whether the format uses a dynamic color scale or a measure evaluated in filter context. If the business meaning must remain fixed, use stable thresholds and test the report with its common slicer selections.

When troubleshooting, use a small sample visual with a few known values. Include a blank and values on both sides of each threshold. This makes it easier to distinguish a formatting mistake from a data-model issue, an unexpected aggregation, or a filter interaction.

Conditional formatting versus fixed formatting

Fixed formatting is appropriate when appearance should not change with the data—for example, using one brand color for a chart series or bolding a header. Conditional formatting is appropriate when the visual signal itself carries information, such as identifying an overdue item or showing which products exceed a target.

There is also a difference between conditional formatting and a filter. Formatting leaves the underlying rows visible and highlights them; a filter removes or limits what appears. If a manager needs to see every order while noticing late ones, conditional formatting may help. If the report should show only late orders, apply a filter instead. Neither approach changes the source data.

Frequently Asked Questions

What does conditional formatting mean in Power BI?
It means changing a supported visual property according to data, a rule, or a measure. Common examples include coloring table cells by target status, adding data bars, and assigning colors to chart values.

Can I conditionally format a Power BI chart?
Some chart types support conditional data colors, but the options vary by visual and field arrangement. Select the chart and check Format visual for a color setting with an fx option. If it is unavailable, consider a supported visual or a different way to communicate the result.

Can conditional formatting be based on a DAX measure?
Yes. A measure can return a color value, such as a hexadecimal color code, and be selected as the field value for a supported formatting option. The measure’s result depends on the visual’s filter context, so test it in the actual report.

Does conditional formatting change the values in my data?
No. It changes how supported visual elements are displayed, not the values stored in the model or source. It can make a value more noticeable, but it does not correct errors or replace an explanation of what the metric means.

Why does the color change when I use a slicer?
A slicer changes the data context used by the visual. A measure may return a different result, and a color scale may recalculate its range for the filtered data. Use fixed business thresholds when the status should not depend on the other visible values.

Is conditional formatting available in Power BI Service?
Power BI Service supports viewing reports and, when a user has the appropriate permissions and is editing a report, offers report-editing capabilities. The available controls can depend on the report, visual, permissions, and interface updates. Power BI Desktop is commonly used to author and verify formatting before publishing.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.