20. Dynamic Text and Dynamic Colour (Conditional Formatting)
20.6 Conditional Formatting: Rules, Gradient and Field Value
Conditional formatting changes colours or icons based on values. There are three format styles:
| Format style | How it works | Example |
|---|---|---|
| Gradient (colour scale) | Lowest value → one colour, highest → another (optional middle colour) | Avg Delivery Time: green (low) → yellow → red (high) |
| Rules | If value is in a range, then use a colour | ≤ 10 green, > 10 and ≤ 15 amber, > 15 red |
| Field value | A column or measure returns the colour (name or hex code like #D13438) |
A DAX colour measure (reusable everywhere) |
The DAX colour measure (field value):
Delivery Time Colour =
VAR t = [Avg Delivery Time (mins)]
RETURN
SWITCH(TRUE(),
ISBLANK(t), BLANK(),
t <= 10, "#107C10", -- green
t <= 15, "#F2A900", -- amber
"#D13438") -- red: slower than 15 minutes
Steps in Power BI – colour the Avg Delivery Time column in a table
- Build a table: Store Name, Total Orders, Avg Delivery Time (mins), Cancellation Rate.
- In Build visual, click the drop-down arrow on Avg Delivery Time (mins) in Columns › Conditional formatting › Background color. (Or Format visual › Visual › Cell elements › Apply settings to the column › Background color On › fx.)
- Format style: Rules. Apply to: Values only. What field should we base this on?: Avg Delivery Time (mins). Summarization: (measure).
- Rules: If value ≥ 0 Number and ≤ 10 Number then green; > 10 and ≤ 15 amber; > 15 and ≤ 999 red. Click + New rule for each › OK.
- Or choose Format style Field value ›
[Delivery Time Colour]› OK. The same colours are now controlled by one measure. - Font color: use white font for the red cells with another field-value measure.
Empty values
In the Gradient dialog, How should we format empty values? lets you choose how blanks are shown (for example Don't format or a specific colour). Cancelled-only stores with no delivery time will not look "perfect green".
Conditional formatting ne report "bolto". Pan rang jast vapru naka – fakt mahatvache highlight kara.
Ravindra Bagale's Tip
Mitrano, khup students use a colour gradient on a measure where low is good (delivery time) with green at the high end. Check the direction of every colour rule, or drive the colour from a measure that returns a colour name or hex code based on business thresholds. He exam aani interview doghansathi important aahe.