Conditional Formatting
Power BI · Data Visualization
Theory
Conditional formatting changes how values look based on the values themselves: background colour, font colour, data bars, icons or web URLs in tables and matrices, and data colours in many charts. You can format by Gradient (colour scale from lowest to highest), by Rules (if value is between X and Y, use this colour), or by Field value, where a measure returns the colour name or hex code to use.
For example, in a table of regions you can set the Total Sales background with rules: at least ₹3,000 green, at least ₹1,000 amber, otherwise red. North (₹3,200) turns green, West (₹2,400) amber and South (₹600) red. The Field value option is the most flexible: a measure using SWITCH can return "#2E7D32" or "#C62828" and the same logic can colour a table, a bar chart and a card consistently.
Why we use it
Colour draws the eye to what needs attention. Readers spot underperforming regions or products instantly instead of scanning every number in a table.
When to use it
Use it to highlight exceptions and thresholds the business cares about. Avoid colouring everything or using red and green alone, which colour-blind readers struggle with; add icons or labels as well.
How it works
- Choose a value to highlight
- Open Cell elements or chart Colors → fx
- Is the logic a fixed threshold? Yes: Use Rules. No: Use a Field value measure or Gradient.
- Set colours and test with real values
- Exceptions stand out on the page
Syntax
Table/matrix: Format → Cell elements → choose series → Background color / Font color / Data bars / Icons → fx
Format style: Gradient | Rules | Field value
Charts: Format → Bars/Columns → Colors → fxExample
// Field value approach: a measure returns the colour
Sales Colour =
SWITCH(
TRUE(),
[Total Sales] >= 3000, "#2E7D32", // green
[Total Sales] >= 1000, "#F9A825", // amber
"#C62828" // red
)
// Format → Cell elements → Background color → fx → Format style: Field value → Sales ColourKey points
- Format by Gradient, Rules or Field value
- Field value lets a DAX measure return the colour
- Works for background, font, data bars, icons and chart colours
- Rules are checked against the values you set; test edge values
- Do not rely on colour alone; add icons or labels