Power BI Conditional Formatting with a Measure

Power BI guide Home / Guides / Power BI

Apply Power BI conditional formatting with a DAX measure that returns clear status, colour or threshold values.

2026-06-16 8 min read min read Digital Adaption On this page

1. Define the rule
2. Write the formatting measure
3. Apply and test it

Conditional formatting is useful when it helps users act faster. It becomes a problem when colours are hard-coded differently in every visual and nobody can explain the threshold. This guide shows a measure-led approach that keeps formatting consistent. For broader reporting repair work, see the Power BI consultancy and ERP reporting analytics pages.

Practical answer

Create a business measure first, create a separate formatting measure that returns a hex colour or status, then apply it through conditional formatting by field value.

1 Define the rule

Do not start with colours. Start with the operational rule. For example, OTIF below 95 percent might be amber, below 90 percent red, and 95 percent or above green. Those thresholds should come from the process owner.

If the rule varies by customer, site or product group, capture that in data rather than creating separate visuals with separate hard-coded settings.

• Name the KPI being formatted.
• Document the threshold and owner.
• Decide what should happen when the value is blank.
• Use accessible colours and do not rely on colour alone.

2 Write the formatting measure

Keep the formatting measure separate from the KPI measure. The KPI should return a number. The formatting measure should return a colour code or label.

SWITCH TRUE is readable for threshold logic and easy for another analyst to review.

OTIF % =
DIVIDE ( [Orders On Time In Full], [Orders Due] )

OTIF Colour =
SWITCH (
    TRUE (),
    ISBLANK ( [OTIF %] ), "#94A3B8",
    [OTIF %] < 0.90, "#DC2626",
    [OTIF %] < 0.95, "#F59E0B",
    "#16A34A"
)

3 Apply and test it

In the visual formatting pane, choose conditional formatting, select field value, and point Power BI at the colour measure. Test it at row level and total level because totals may have different context from individual rows.

Where the report is operationally important, add a tooltip or small legend explaining what the colours mean. Users should not have to guess the threshold.

1. Apply the measure to font colour, background colour or data bars as appropriate.
2. Check blank values, zero values and totals.
3. View the report in Power BI Service, not only Desktop.
4. Ask the process owner to sign off the threshold wording.

Common mistakes to avoid

Putting KPI logic inside the colour measure only

If the number and colour are calculated separately, users cannot audit the result. Keep the numeric KPI visible somewhere.

Using colours without labels

Colour-only reports are harder to use and easier to misread. Add a label, tooltip or status field where the decision matters.

Forgetting total context

A row may be red while the total is green because the total is recalculated, not averaged from row colours.

Validation checklist

• The threshold is written down and owned by the business.
• The DAX measure returns valid hex colours.
• Blank and zero values are handled deliberately.
• The total row has been checked separately.
• The report includes a clear label or legend for the status.

Need the report to reconcile with ERP?

Digital Adaption helps UK SMEs rebuild trusted Power BI, ERP and operational reporting after migrations, ownership gaps and model drift.

Review reporting trust

FAQ

Can a DAX measure return icons?

It can return text or Unicode-style symbols, but built-in icon sets are usually easier to maintain. Use a colour/status measure when you need consistent logic across visuals.

Why is conditional formatting unavailable?

Some visual fields and custom visuals do not support every formatting option. Check that the field is in the correct bucket and use field value formatting where available.

Start with a 30-minute data risk call