TECH **Power BI Conditional Formatting: Zero-Fluff Study Guide**

Power BI Conditional Formatting: Zero-Fluff Study Guide

(Background, Font, Icons, KPI Indicators)

1. What This Is & Why It Matters

Conditional formatting in Power BI lets you dynamically change the appearance of visuals (tables, matrices, cards, etc.) based on data rules. Think of it like a traffic light for your reports: - Green = Good (e.g., sales above target).
- Red = Bad (e.g., inventory below threshold).
- Yellow = Warning (e.g., approaching budget limit).

Why it matters in production:
- Stakeholders skim reports in seconds. If your data doesn’t visually scream what’s important, they’ll miss critical insights.
- Manual formatting is a time sink. If you hardcode colors, you’ll waste hours updating reports when thresholds change (e.g., "Now 80% is the new 90%").
- Dashboards without conditional formatting look amateur. Clients and executives expect dynamic, data-driven visuals—not static Excel exports.

Real-world scenario:
You’re building a sales dashboard for a retail chain. The VP of Sales wants to see: - Stores in the red (sales < 80% of target) highlighted immediately.
- Top performers (sales > 120% of target) flagged with a ? icon.
- Trends over time where negative growth is bolded in red.

Without conditional formatting, you’d manually color-code every cell—or worse, they’d miss a crisis until it’s too late.

2. Core Concepts & Components

3. Step-by-Step Hands-On: Applying Conditional Formatting

Prerequisites

• Power BI Desktop installed.
• A dataset with numeric/ categorical data (e.g., sales, inventory, survey results).
• Basic familiarity with Power BI visuals (tables, matrices, cards).

Task: Build a Sales Dashboard with Conditional Formatting

We’ll format a matrix visual to highlight: 1. Stores below target (red background).
2. Top performers (green font + ? icon).
3. Negative growth (bold red font).

Step 1: Load Data

1. Open Power BI Desktop.
2. Click Home > Get Data > Excel (or use a sample dataset like SalesData.xlsx).
3. Load a table with columns: Store, Sales, Target, Growth%.

Step 2: Create a Matrix Visual

1. Click the Matrix icon in the Visualizations pane.
2. Drag Store to Rows.
3. Drag Sales, Target, and Growth% to Values.

Step 3: Apply Background Color (Stores Below Target)

1. Click the Format paint roller (Visualizations pane).
2. Expand Conditional formatting > Background color.
3. Select the Sales field.
4. Set:
5. Format by: Field value
6. Based on field: Sales
7. Summarization: Sum
8. Rules:
   • If value < [Target], then red (hex: #FF6B6B).
   • If value >= [Target], then white (hex: #FFFFFF).
9. Click OK.

Verification: Stores with Sales < Target now have a red background.

Step 4: Apply Font Color + Icons (Top Performers)

1. In the Format pane, go to Conditional formatting > Font color.
2. Select the Sales field.
3. Set:
4. Format by: Field value
5. Based on field: Sales
6. Rules:
   • If value > 1.2 * [Target], then green (hex: #2ECC71).
   • Else, black (hex: #000000).
7. Click OK.

Add Icons: 1. In the Format pane, go to Conditional formatting > Icons.
2. Select the Sales field.
3. Set:
- Icon layout: Right of data
- Rules:
- If value > 1.2 * [Target], show ? (unicode: U+1F3C6).
- Else, show no icon.
4. Click OK.

Verification: Top-performing stores now have green text + a ? icon.

Step 5: Apply Bold Red Font (Negative Growth)

1. In the Format pane, go to Conditional formatting > Font.
2. Select the Growth% field.
3. Set:
4. Format by: Field value
5. Based on field: Growth%
6. Rules:
   • If value < 0, then bold + red (hex: #E74C3C).
   • Else, normal + black.
7. Click OK.

Verification: Negative growth values are bold and red.

Step 6: Add KPI Indicators to a Card

1. Add a Card visual to your report.
2. Drag Growth% to the card.
3. In the Format pane, go to Conditional formatting > Indicators.
4. Set:
5. Style: Shapes
6. Rules:
   • If value > 0, show ▲ (green).
   • If value = 0, show → (gray).
   • If value < 0, show ▼ (red).
7. Click OK.

Verification: The card now shows a directional arrow based on growth.

4. ? Production-Ready Best Practices

Design & UX

• Limit colors: Stick to 3-4 max (e.g., red/yellow/green + neutral). Use tools like Coolors to pick accessible palettes.
• Avoid rainbow gradients: They’re hard to interpret. Use sequential (light → dark) or diverging (red → white → blue) palettes.
• Test for colorblindness: Use Color Oracle to simulate deuteranopia. Avoid red/green for critical alerts.
• Icons > text: A ? is faster to scan than "CRITICAL". Use unicode symbols (e.g., U+26A0 for ⚠️) or Power BI’s built-in sets.

Performance

• Avoid over-formatting: Conditional formatting on large tables (>10K rows) can slow rendering. Test with your dataset.
• Cache custom icons: If using web icons (SVG/PNG), host them on a CDN or embed in the report to avoid latency.
• Use DAX measures sparingly: Complex DAX for formatting can slow queries. Pre-calculate thresholds in Power Query if possible.

Maintainability

• Document rules: Add a text box to your report explaining formatting logic (e.g., "Red = <80% of target").
• Use parameters: Store thresholds in a parameter table (e.g., Thresholds[MinSales] = 80) so you can update them in one place.
• Version control: Save PBIX files with clear names (e.g., SalesDashboard_v2_ConditionalFormatting.pbix).

Security

• Hide sensitive thresholds: If formatting reveals confidential targets (e.g., "CEO Bonus Threshold"), use row-level security (RLS) to restrict access.
• Avoid hardcoded secrets: If using web icons, don’t embed API keys in URLs.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus (PL-300)

Typical Question Patterns

1. Scenario-based: "You need to highlight sales below target in red. Which conditional formatting option should you use?"
2. Answer: Background color (not font color or icons).

3. Trap: Font color is for text, not cell backgrounds.

4. Icon rules: "Which icon set should you use to show a ? for values below 50?"

5. Answer: Unicode symbols (e.g., U+1F6A8) or Power BI’s built-in "Shapes" set.

6. Trap: Web icons require URLs and may not load in published reports.

7. DAX vs. rules: "When should you use a DAX measure for conditional formatting?"

8. Answer: For complex logic (e.g., "If [Sales] < [Target] AND [Region] = 'West'").

9. Trap: DAX is overkill for simple rules (e.g., "If value > 100").

10. KPI indicators: "Which visual is best for showing a directional arrow based on growth?"

11. Answer: Card visual (not a table or matrix).
12. Trap: KPI indicators don’t work well in tables/matrices.

Key ⚠️ Trap Distinctions

7. ? Hands-On Challenge (with Solution)

Challenge

You have a survey results table with a column [SatisfactionScore] (1-5). Format the table so: - Scores 1-2 = red background + ❌ icon.
- Scores 3 = yellow background.
- Scores 4-5 = green background + ✅ icon.

Solution: 1. Add a matrix visual with [SatisfactionScore] in Values.
2. Go to Conditional formatting > Background color:
- Rules:
- If value <= 2, red (#FF6B6B).
- If value = 3, yellow (#F1C40F).
- If value >= 4, green (#2ECC71).
3. Go to Conditional formatting > Icons:
- Rules:
- If value <= 2, show ❌ (unicode: U+274C).
- If value >= 4, show ✅ (unicode: U+2705).
- Else, no icon.

Why it works: - Background color handles the 3-tier system (red/yellow/green).
- Icons add visual cues for extreme values (❌/✅).

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Official Docs:
2. Microsoft: Conditional formatting in Power BI
3. DAX for conditional formatting
4. Tutorials:
5. Guy in a Cube: Conditional Formatting Deep Dive (Search for latest video)
6. SQLBI: Advanced DAX for formatting
7. Tools:
8. Coolors (color palette generator)
9. Color Oracle (colorblindness simulator)
10. Books:
11. The Definitive Guide to DAX (Marco Russo & Alberto Ferrari) – For advanced DAX formatting.