Power BI Lesson 39 – Advanced DAX | Dataplexa

DAX · Lesson 39

Advanced DAX Patterns

Every Power BI developer eventually hits a set of recurring problems that simple aggregation and CALCULATE alone cannot solve. Dynamic segmentation, ABC classification, Top-N analysis, disconnected slicers, and conditional formatting measures — these patterns appear in almost every real business report. This lesson covers each one completely, with the reasoning behind the approach so you can adapt it when the specific requirement changes.

The Working Dataset

Products and Orders tables — used for all examples

Products (10 rows)

ProductID	ProductName	Category
P01	Laptop Pro	Electronics
P02	Wireless Mouse	Electronics
P03	Office Desk	Furniture
P04	Desk Chair	Furniture
…	6 more products	Various

Orders (summary by product)

Product	Revenue
Laptop Pro	48,000
Desk Chair	22,000
Office Desk	18,000
Wireless Mouse	9,500
6 more products	12,500

Grand total revenue: 110,000

Pattern 1 — ABC Classification

ABC analysis classifies products (or customers, or SKUs) into three tiers based on their cumulative contribution to total revenue. Class A = top products accounting for roughly 70% of revenue, Class B = next 20%, Class C = remaining 10%. The challenge in DAX is that the classification of any one product depends on how all other products rank — it is an inherently comparative calculation.

// ABC Classification — calculated column on Products table
// Requires: Total Revenue measure already defined

ABC Class =
VAR ProductRevenue =
    CALCULATE([Total Revenue])          -- this product's revenue
                                        -- CALCULATE triggers context transition
VAR TotalRevenue =
    CALCULATE([Total Revenue], ALL(Products))  -- grand total, ignores product filter

VAR CumulativeRevenue =
    CALCULATE(
        [Total Revenue],
        FILTER(
            ALL(Products),
            -- Include all products with revenue >= this product's revenue
            -- This gives the cumulative share up to and including this product
            CALCULATE([Total Revenue]) >= ProductRevenue
        )
    )

VAR CumulativePct = DIVIDE(CumulativeRevenue, TotalRevenue)

RETURN
    SWITCH(
        TRUE(),
        CumulativePct <= 0.70, "A",   -- top products, 70% of revenue
        CumulativePct <= 0.90, "B",   -- next tier, cumulative 90%
        "C"                            -- long tail
    )

ABC Classification — all 10 products ranked by revenue

Product	Revenue	Cumulative Rev	Cumulative %	ABC Class
Laptop Pro	48,000	48,000	43.6%	A
Desk Chair	22,000	70,000	63.6%	A
Office Desk	18,000	88,000	80.0%	B
Wireless Mouse	9,500	97,500	88.6%	B
Product 5–10 (avg)	~2,083	110,000	100.0%	C

A items (Laptop Pro + Desk Chair) = 70,000 / 110,000 = 63.6% — just under the 70% threshold · Office Desk pushes cumulative to 80% → B · remaining products are C

Pattern 2 — Top N Filter

A Top-N measure shows a value only for the top N items by a ranking metric and returns BLANK for all others. Combined with a visual-level filter this produces clean Top-5 or Top-10 charts. The pattern uses RANKX with ALLSELECTED so the N tracks the user's slicer selection.

// Top N Revenue — only show revenue for the top N products
// N is hard-coded here; see Pattern 3 for a dynamic N slicer

Top 3 Revenue =
VAR CurrentRank =
    RANKX(
        ALLSELECTED(Products[ProductName]),
        [Total Revenue],
        ,
        DESC,
        DENSE
    )
RETURN
    IF(CurrentRank <= 3, [Total Revenue], BLANK())

// "Others" revenue — everything not in the Top N
Others Revenue =
VAR CurrentRank =
    RANKX(
        ALLSELECTED(Products[ProductName]),
        [Total Revenue],
        ,
        DESC,
        DENSE
    )
RETURN
    IF(CurrentRank > 3, [Total Revenue], BLANK())

Top 3 Revenue — product table visual

Product	Total Revenue	Rank	Top 3 Revenue
Laptop Pro	48,000	1	48,000
Desk Chair	22,000	2	22,000
Office Desk	18,000	3	18,000
Wireless Mouse	9,500	4	BLANK
…others	12,500	5–10	BLANK

Rank 4–10 return BLANK · add a visual-level filter "Top 3 Revenue is not blank" to remove those rows from the chart entirely

Pattern 3 — Disconnected Slicer Table

A disconnected table is a standalone table with no relationship to any other table in the model. When placed in a slicer, its selected value is captured by a measure using SELECTEDVALUE(). The measure then uses that value to control behaviour — for example, letting the user choose N for a Top-N chart, or select a metric to display.

// Step 1 — Create a disconnected table in DAX (or Power Query)
// DAX calculated table:
TopN Selection =
    DATATABLE(
        "N", INTEGER,
        {{3}, {5}, {10}, {20}}
    )
// This creates a 4-row table with values 3, 5, 10, 20
// Place TopN Selection[N] in a slicer — NO relationship to any fact table

// Step 2 — Capture the selected value in a measure
Selected N = SELECTEDVALUE('TopN Selection'[N], 5)
// Returns the slicer selection, defaults to 5 if nothing selected

// Step 3 — Use it in the Top N measure
Dynamic Top N Revenue =
VAR N = SELECTEDVALUE('TopN Selection'[N], 5)
VAR CurrentRank =
    RANKX(
        ALLSELECTED(Products[ProductName]),
        [Total Revenue],
        ,
        DESC,
        DENSE
    )
RETURN
    IF(CurrentRank <= N, [Total Revenue], BLANK())

Dynamic Top N — slicer controls how many products appear

N slicer = 3 → shows top 3

Product	Revenue
Laptop Pro	48,000
Desk Chair	22,000
Office Desk	18,000

N slicer = 5 → shows top 5

Product	Revenue
Laptop Pro	48,000
Desk Chair	22,000
Office Desk	18,000
Wireless Mouse	9,500
Product 5	5,200

No relationship between TopN Selection and any fact table · SELECTEDVALUE reads the slicer selection · changing the slicer instantly changes which products appear

Pattern 4 — Dynamic Metric Slicer

The same disconnected table pattern allows users to choose which metric a chart displays — Revenue, Orders, Margin — from a slicer. One chart, multiple KPIs, no page navigation required.

// Step 1 — Disconnected metric table
Metric Selection =
    DATATABLE(
        "MetricName",  STRING,
        "SortOrder",   INTEGER,
        {
            {"Revenue",      1},
            {"Order Count",  2},
            {"Gross Margin", 3},
            {"Avg Order",    4}
        }
    )
// Place Metric Selection[MetricName] in a slicer (single select)

// Step 2 — Switch measure that returns the selected metric
Selected Metric =
VAR Choice = SELECTEDVALUE('Metric Selection'[MetricName], "Revenue")
RETURN
    SWITCH(
        Choice,
        "Revenue",      [Total Revenue],
        "Order Count",  [Order Count],
        "Gross Margin", [Gross Margin %],
        "Avg Order",    [Avg Order Value],
        [Total Revenue]   -- default
    )

// Step 3 — Dynamic axis label (use in a card or chart title)
Selected Metric Label =
    SELECTEDVALUE('Metric Selection'[MetricName], "Revenue")

Selected Metric — same bar chart, different slicer selections

Slicer = Revenue

110,000

Slicer = Order Count

342

Slicer = Gross Margin

38.2%

One measure, one chart — the slicer controls which KPI renders · use Selected Metric Label in the chart title to show which metric is active

Pattern 5 — Conditional Formatting Measures

Conditional formatting in Power BI can be driven by a measure — a measure that returns a hex colour string or a number between 0 and 1. This enables fully dynamic colouring based on business rules: green if above target, red if below, amber in between.

// Pattern A — Colour measure returning a hex string
// Use in: Table/Matrix → Conditional Formatting → Font colour → Field value
Revenue Colour =
VAR Rev = [Total Revenue]
VAR Target = 20000          -- target revenue per product
RETURN
    SWITCH(
        TRUE(),
        Rev >= Target * 1.1,  "#16a34a",   -- >110% of target: green
        Rev >= Target,         "#0369a1",   -- 100-110%: blue
        Rev >= Target * 0.8,   "#eab308",   -- 80-100%: amber
        "#dc2626"                           -- <80%: red
    )

// Pattern B — Traffic light icon measure (returns 0, 1, or 2)
// Use in: Conditional Formatting → Icons → Field value
// 0 = red circle, 1 = amber, 2 = green
Revenue Traffic Light =
VAR Rev = [Total Revenue]
VAR Target = 20000
RETURN
    SWITCH(
        TRUE(),
        Rev >= Target,         2,   -- green
        Rev >= Target * 0.8,   1,   -- amber
        0                           -- red
    )

// Pattern C — Background heatmap (0.0 to 1.0 scale)
// Use in: Conditional Formatting → Background colour → Field value
// Set colour scale: 0=red, 0.5=amber, 1=green in Power BI
Revenue Heatmap =
    DIVIDE(
        [Total Revenue] - MIN(Products[MinRevenue]),
        MAX(Products[MaxRevenue]) - MIN(Products[MinRevenue])
    )
-- Returns 0.0 for lowest revenue product, 1.0 for highest

Revenue Colour measure — applied to product table (Target = 20,000)

Product	Revenue	vs Target	Revenue Colour
Laptop Pro	48,000	+140%	#16a34a GREEN
Desk Chair	22,000	+10%	#0369a1 BLUE
Office Desk	18,000	-10%	#eab308 AMBER
Wireless Mouse	9,500	-53%	#dc2626 RED

Apply in Power BI: select Revenue column → Format → Conditional formatting → Font colour → Field value → select Revenue Colour measure

Pattern 6 — Dynamic Segmentation (What-If)

Dynamic segmentation lets users adjust a threshold via a slicer and instantly see which customers, products, or orders fall into each segment. Unlike a calculated column (fixed at refresh), the segments update live as the slicer changes.

// Disconnected threshold table
Revenue Threshold =
    DATATABLE(
        "Threshold", INTEGER,
        {{5000},{10000},{20000},{50000}}
    )

// Dynamic segment measure
Revenue Segment =
VAR Threshold = SELECTEDVALUE('Revenue Threshold'[Threshold], 20000)
VAR Rev = [Total Revenue]
RETURN
    IF(
        ISBLANK(Rev),
        BLANK(),
        IF(Rev >= Threshold, "Above Threshold", "Below Threshold")
    )

// Count products in each segment
Products Above Threshold =
VAR Threshold = SELECTEDVALUE('Revenue Threshold'[Threshold], 20000)
RETURN
    COUNTROWS(
        FILTER(
            VALUES(Products[ProductName]),
            [Total Revenue] >= Threshold
        )
    )

Dynamic Segmentation — threshold slicer changes the split

Threshold slicer = 20,000

Segment	Products
Above Threshold	2
Below Threshold	8

Threshold slicer = 10,000

Segment	Products
Above Threshold	3
Below Threshold	7

Threshold 20,000: only Laptop Pro (48K) and Desk Chair (22K) qualify → 2 above · Threshold 10,000: adds Wireless Mouse (9.5K)... wait, 9.5K < 10K → still 3: Laptop Pro, Desk Chair, Office Desk qualify

Pattern 7 — Last Refresh Timestamp

A common report requirement is showing when the data was last refreshed. The cleanest approach creates a single-row calculated table at refresh time whose value is the current datetime — and a measure that reads from it.

// Calculated table — created at refresh, stores the refresh time
RefreshTime =
    ROW("LastRefresh", NOW())
// ROW() creates a single-row table with a named column
// NOW() evaluates at refresh time and is stored as the column value

// Measure — reads the stored refresh time
Last Refreshed =
    FORMAT(
        MAX(RefreshTime[LastRefresh]),
        "DD MMM YYYY hh:mm"
    )
// MAX() aggregates the single value (required to return a scalar)
// FORMAT() converts it to a readable text string
// Use in a card visual with title "Data as of"

Last Refreshed — card visual output

Data as of

16 Mar 2026  09:42

RefreshTime table has one row · NOW() evaluated at the moment the data model was last refreshed · FORMAT() returns text so the card shows a string, not a date axis · update format string for local conventions

Teacher's Note: The disconnected slicer table pattern is the most versatile technique in this lesson and the one that transforms a good Power BI report into a great one. A report where the user selects "Top 5 or Top 10 or Top 20" from a slicer, or switches the chart between Revenue and Margin with a button, feels like a proper application rather than a static export. The key insight is that the disconnected table has no relationship — and that is intentional. It participates in the filter context only through SELECTEDVALUE(), which lets the measure capture the user's choice without any cross-filtering side effects. Once you understand this pattern, you will use it in almost every report you build.

Practice

Practice 1 of 3

In the ABC classification pattern, the cumulative revenue for each product is computed by using CALCULATE with a FILTER that includes all products whose revenue is ___ or equal to the current product's revenue — giving the sum of all revenue at or above the current rank position.

Practice 2 of 3

A disconnected slicer table works without a relationship to any fact table. The measure captures the user's selection using the function ___(table[column], defaultValue), which returns the single selected value or the default when nothing or multiple values are selected.

Practice 3 of 3

For a conditional formatting measure to drive font colour in a table visual, it must return a ___ value — specifically a hex colour code string like "#16a34a" — which Power BI reads as the colour to apply to that cell.

Lesson Quiz

Quiz 1 of 3

A colleague builds an ABC classification as a calculated column. It works perfectly when all data is shown. When they apply a Category slicer to show only Furniture products, the ABC classes still reflect the ranking across all 10 products rather than ranking within Furniture only. Why, and how should it be fixed?

Calculated columns ignore slicers by design — add a KEEPFILTERS wrapper around the cumulative revenue CALCULATE to force it to respect the Category slicer Calculated columns are computed at refresh time against all rows — the slicer was not active then, so the ABC class is fixed regardless of filters. To get a slicer-aware ABC classification, the ABC measure must be rebuilt as a DAX measure (not a calculated column) using ALLSELECTED instead of ALL in the cumulative revenue CALCULATE, so the ranking respects the active Category filter. The Category slicer cross-filters the Products table and calculated columns automatically update to use only the filtered rows — if the ABC class is not changing, the slicer relationship direction is wrong and needs to be reversed in Model View Replace ALL(Products) with ALLEXCEPT(Products, Products[Category]) in the cumulative revenue formula — ALLEXCEPT preserves the Category filter from the slicer while removing all other filters from the Products table

Quiz 2 of 3

You add a TopN Selection disconnected table to the model and use SELECTEDVALUE('TopN Selection'[N], 5) in a measure. A user selects both "5" and "10" in the slicer simultaneously. What does the measure return, and why?

10 — when multiple values are selected, SELECTEDVALUE returns the maximum of all selected values so the measure uses the larger of the two selections 5 — when multiple values are selected, SELECTEDVALUE returns the minimum to prevent showing more items than the most restrictive selection 5 — the default value. SELECTEDVALUE returns the alternate result (second argument) whenever there is not exactly one value selected. Both 5 and 10 are selected, so SELECTEDVALUE cannot determine a single value and falls back to the default of 5. An error — SELECTEDVALUE throws an error when multiple values are selected; the measure must be wrapped in IFERROR to handle multi-select scenarios

Quiz 3 of 3

A conditional formatting measure for background colour returns values between 0 and 1. You set the colour scale in Power BI to: 0 = red, 0.5 = amber, 1 = green. The lowest-revenue product always shows green and the highest-revenue product shows red. What went wrong?

The numerator and denominator in the heatmap DIVIDE are inverted — the formula is returning values close to 1 for low-revenue products and close to 0 for high-revenue products. Swap the colour scale in Power BI (make 0 = green, 1 = red) or fix the formula so the highest revenue returns 1 and lowest returns 0. The colour scale must use the exact values 0, 0.5, and 1 as anchor points — if any product's heatmap value is exactly 0.5, Power BI maps the entire scale incorrectly and reverses the gradient Power BI's colour scale always applies colours in reverse order when the field value is from a measure — use a calculated column instead so the colour scale applies in the correct direction The DIVIDE formula returns decimal values which Power BI interprets as percentage thresholds — multiply the result by 100 so Power BI receives values 0–100 instead of 0–1, which forces the colour scale to apply in the correct direction

Next up — Lesson 40 covers DAX Best Practices, including naming conventions, measure organisation, performance optimisation, documentation patterns, and the habits that keep complex models maintainable as they grow.

← Previous Course Index Next →

Power BI Course