Segmentation And Conditional Logic

What you will learn

• Create clear segments (High/Medium/Low, Bands) with DAX using IF and SWITCH(TRUE()).
• Build conditional KPIs (e.g., Ahead/On Track/Behind) that react to context.
• Calculate conditional totals using CALCULATE with filter expressions.
• Handle tricky cases safely (blanks, divide-by-zero, ordering conditions).

Who this is for

• BI Developers and Analysts building Power BI models and KPI dashboards.
• Anyone who needs consistent customer/product segmentation and conditional metrics.

Prerequisites

• Basic DAX: measures vs. columns, SUM, CALCULATE.
• Star schema familiarity (Fact + Dimension tables) and relationships.

Why this matters

Business decisions depend on comparing groups: high-value customers vs. others, healthy margins vs. risky ones, orders on time vs. late. As a BI Developer, you will:

• Bucket customers into tiers to prioritize sales and service.
• Flag products into margin bands for pricing decisions.
• Show red/amber/green KPIs to leaders for fast action.
• Compute conditional totals: e.g., Sales from "High" customers.

Concept explained simply

Segmentation = assigning categories based on rules. Conditional logic = picking outputs based on conditions. In DAX, you typically use:

• IF(condition, result_if_true, result_if_false)
• SWITCH(TRUE(), cond1, result1, cond2, result2, ..., default) for multiple ordered conditions
• CALCULATE(measure, condition) to compute a measure only for rows that meet a condition
• Safety helpers: DIVIDE(x,y,0) and COALESCE(value, fallback)

Worked examples

1) Product margin bands (calculated column)

Goal: Label each product with a margin band for slicing.

// In Products table
Products[Margin %] = DIVIDE(Products[Profit], Products[Sales], 0)

Products[Margin Band] =
VAR m = Products[Margin %]
RETURN
    SWITCH(TRUE(),
        m >= 0.40, "A: Very High",
        m >= 0.25, "B: High",
        m >= 0.15, "C: Medium",
        m >  0.00, "D: Low",
        "E: Negative or Zero"
    )

Tip: Columns are great for categorical slices and row-level filtering.

2) Conditional KPI label and color (measures)

Goal: Show "Ahead / On Track / Behind" based on Sales vs Target and color it.

Total Sales = SUM(Sales[Amount])
Target Sales = SUM(Targets[TargetAmount])
Sales vs Target % = DIVIDE([Total Sales], [Target Sales], 0)

Sales KPI =
VAR pct = [Sales vs Target %]
RETURN
    SWITCH(TRUE(),
        ISBLANK(pct), "No Target",
        pct >= 1.05,  "Ahead",
        pct >= 0.95,  "On Track",
        "Behind"
    )

// For conditional formatting by field value
Sales KPI Color =
VAR label = [Sales KPI]
RETURN
    SWITCH(label,
        "Ahead",    "#1a9850",
        "On Track", "#66bd63",
        "Behind",   "#d73027",
        "#bdbdbd" // No Target / default
    )

Use the color measure for visual conditional formatting.

3) Customer revenue tiers and conditional totals

Goal: Tier customers and compute sales from High-tier customers.

// Precompute lifetime sales per customer as a column (simple & efficient)
Customers[Lifetime Sales] = SUMX(RELATEDTABLE(Sales), Sales[Amount])

Customers[Revenue Segment] =
VAR s = Customers[Lifetime Sales]
RETURN
    SWITCH(TRUE(),
        s >= 100000, "High",
        s >= 50000,  "Medium",
        "Low"
    )

// Conditional total (measure)
Sales from High Customers =
    CALCULATE([Total Sales], Customers[Revenue Segment] = "High")

Customer Revenue Segment (Measure) =
VAR sales = [Total Sales]
RETURN
    IF(ISBLANK(sales), BLANK(),
        SWITCH(TRUE(),
            sales >= 100000, "High",
            sales >= 50000,  "Medium",
            "Low"
        )
    )

How to approach segmentation reliably

1. Define business rules clearly and order them from strictest to broadest.
2. Use SWITCH(TRUE()) for multiple bands; avoid deep nested IFs.
3. Use DIVIDE and COALESCE to handle zero/blank safely.
4. Prefer columns for static segment labels; use measures for dynamic, context-aware flags.
5. For conditional totals, wrap your base measure with CALCULATE and a boolean filter.

Common mistakes and self-check

• Unordered conditions: Placing a broad rule first hides later rules. Fix: Order from most specific to least specific.
• Divide-by-zero: Using x/y directly. Fix: Use DIVIDE(x,y,0).
• Using a text measure as slicer: Not possible. Fix: Create a calculated column for segment labels.
• Blank handling: IF([Measure] > 0) ignores blanks. Fix: COALESCE([Measure],0) or handle ISBLANK.
• Overusing nested IF: Hard to maintain. Fix: SWITCH(TRUE()).

Exercises

Do these now. Then compare with the solutions below each exercise.

1. Exercise 1: Build a Margin Band column in Products using DIVIDE and SWITCH(TRUE()).
2. Exercise 2: Create a Sales KPI measure (Ahead/On Track/Behind/No Target) based on Sales vs Target % and a color measure for formatting.

• Checklist:
  • All bands are mutually exclusive and cover all cases.
  • Zero/blank targets do not error.
  • Color outputs are valid hex codes.

Mini challenge

Extend Example 3 by adding a measure "% Sales from High Customers" and display it as a card that turns green when >= 60%, amber when 40%–59%, and red otherwise.

Practical projects

• Customer Lifetime Value tiers: Build tier labels as columns and a report page summarizing metrics by tier.
• Margin health dashboard: Band products by margin, add conditional KPI colors, and show trend by month.
• Operational SLA tracker: Flag orders as On-time/Late using thresholds and compute conditional counts per team.

Learning path

• Master IF, SWITCH(TRUE()), DIVIDE, COALESCE.
• Practice with calculated columns vs measures for segmentation.
• Use CALCULATE with boolean filters for conditional totals.
• Add conditional formatting measures to make results visible.

Quick Test notice

Anyone can take the quick test below for free. Sign in to save your progress and resume later.

Next steps

• Finish the exercises and take the Quick Test.
• Apply one practical project to your own dataset.
• Move on to more advanced patterns (disconnected parameter tables and dynamic thresholds) once you are comfortable here.