Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 56 ยท DAX

Common DAX Patterns: Segmentation, Cohorts and Dynamic Measures

Learn reusable DAX patterns: a SWITCH measure selector with a disconnected table, ABC classification, new vs returning customers and field parameters, with pandas checks.

After the basics, most DAX work is a handful of patterns that come back again and again. A report needs a slicer that switches the chart between revenue and profit. A manager wants to know which products make up 80% of sales. Marketing asks how many customers in March were new and how many came back. None of these needs a new function. Each one combines the ideas you already know: filter context, CALCULATE, variables and iterators.

This lesson walks through four patterns: the disconnected-table selector, ABC classification, new versus returning customers and field parameters. The DAX is read-only, because Power BI cannot run on this site. Next to each pattern you get a pandas stand-in that produces the same numbers, so you can see the logic work. If CALCULATE still feels shaky, review CALCULATE and Filter Context and Ranking, Top N and Percent of Total first.

Four patterns worth knowing

Selector One visual, many measures.
ABC classes Group items by share of sales.
New vs returning Compare against first purchase.
Field parameters Let users swap fields.

Each pattern is filter context plus a little logic.

1 / 2

Pattern 1: the disconnected table and a SWITCH selector

Suppose a column chart should show revenue, profit or units, depending on what the reader picks. You could build three charts, but one chart and one slicer is cleaner.

The trick is a disconnected table: a small table with a single column of choices that has no relationship to anything in the model. You can create it with a calculated table:

DAX
Metric =
DATATABLE(
    "Metric", STRING,
    "Order", INTEGER,
    {
        { "Revenue", 1 },
        { "Profit", 2 },
        { "Units", 3 }
    }
)

Put Metric[Metric] in a slicer. Because the table is not related to Sales, picking a value in the slicer filters only the Metric table itself. Nothing in the facts changes. The selection becomes an input that a measure can read with SELECTEDVALUE:

DAX
Selected Value =
SWITCH(
    SELECTEDVALUE(Metric[Metric], "Revenue"),
    "Revenue", [Total Revenue],
    "Profit",  [Total Profit],
    "Units",   [Total Units],
    BLANK()
)

SELECTEDVALUE returns the chosen value when exactly one is selected and the second argument (here "Revenue") when none or several are selected. SWITCH then returns the measure that matches. Set Sort by column on Metric[Metric] to Metric[Order] so the slicer lists the choices in your order, not alphabetically. Format strings are the usual snag: revenue wants currency and units want a whole number, so many reports also use a FORMAT call or a dynamic format string on the measure.

The pandas stand-in below follows the same steps: the slicer value is an input, a function picks the right aggregation and the same selector is evaluated once per region, like a chart would.

Python A measure selector in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The chart's axis (the regions) is the filter context, and the slicer is a plain input. When nothing is selected, the default of "Revenue" gives the same totals as the first slicer value. In Power BI the disconnected table is exactly what makes the slicer an input rather than a filter.

Pattern 2: ABC classification

The Pareto idea says a small share of items produces most of the result. ABC classification makes that concrete: class A holds the items that together make up the first 80% of revenue, class B the next 15% and class C the long tail. The cut-offs (80 and 95) are a convention, not a law, so make them parameters if the business wants different ones.

The measure needs, for each product, the revenue of all products that sell at least as much as it, as a share of the total:

DAX
ABC Class =
VAR ProductRevenue = [Total Revenue]
VAR AllProducts = ALLSELECTED(Product[ProductName])
VAR CumulativeRevenue =
    SUMX(
        FILTER(AllProducts, [Total Revenue] >= ProductRevenue),
        [Total Revenue]
    )
VAR TotalRevenue = CALCULATE([Total Revenue], AllProducts)
VAR CumulativeShare = DIVIDE(CumulativeRevenue, TotalRevenue)
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(ProductRevenue), BLANK(),
        CumulativeShare <= 0.80, "A",
        CumulativeShare <= 0.95, "B",
        "C"
    )

Read it in layers. ProductRevenue is captured in a variable before the iteration, so it keeps the value for the current product (see the variables section of CALCULATE and Filter Context). FILTER walks every product in the visible set and keeps those that sell at least as much. Inside FILTER, [Total Revenue] is evaluated for each row through context transition. SUMX adds the survivors up. Dividing by the total gives the cumulative share, and SWITCH(TRUE(), ...) turns the number into a letter, testing conditions in order.

Two details matter. Using ALLSELECTED means the classes adapt to what the reader has selected: filter to one region and the ABC split is recalculated for that region. And products with exactly equal revenue count each other, so the share jumps over their tie together. That is usually fine, but it explains tiny surprises at a boundary.

Here is the same classification in pandas. Sorting, a running total and pd.cut replace the iterator.

Python ABC classification in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two products, P1 and P2, make up exactly 80% of revenue, so they land in class A (the boundary is inclusive). P3 and P4 reach 94% and form class B, and the three smallest products are class C and contribute only 6% together. In a table visual, add ABC Class next to the product names, or use it in a slicer through a calculated column only if the classification should not react to filters. A measure cannot be put on a slicer, which is a limit worth remembering.

The measure recalculates for every row of the visual, and the FILTER over all products makes it quadratic: the cost grows with the square of the product count. That is fine for thousands of products, but test it before using the pattern on a million-row dimension.

Pattern 3: new versus returning customers

A customer is new in a period if their first-ever order falls in that period, and returning if they ordered in the period but first ordered earlier. The word "ever" is the clue: you must look at dates outside the current filter context.

DAX
New Customers =
COUNTROWS(
    FILTER(
        VALUES(Sales[CustomerKey]),
        VAR FirstOrder =
            CALCULATE(MIN(Sales[OrderDate]), REMOVEFILTERS('Date'))
        RETURN
            FirstOrder >= MIN('Date'[Date])
                && FirstOrder <= MAX('Date'[Date])
    )
)

Returning Customers =
DISTINCTCOUNT(Sales[CustomerKey]) - [New Customers]

VALUES(Sales[CustomerKey]) lists the customers active in the current period. For each one, CALCULATE with REMOVEFILTERS('Date') finds the earliest order across all dates (the context transition makes the current customer the filter). If that first order lies inside the period's date range, the customer is new. Returning customers are simply the active customers minus the new ones. Note that other filters, such as a region slicer, stay in force, so "first order" then means first order in that region. If you want the first order anywhere, remove those filters too.

The pandas version finds each customer's first month and compares it with every month they were active in.

Python New vs returning customers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In January both customers are new. In February, Ana and Ben come back and Cy is new. In March, Cy and Ana return, while Dee and Eve are new. The identity Active = New + Returning holds in every month, which is a handy sanity check for the DAX too.

Pattern 4: field parameters

The selector in pattern 1 needs you to write one SWITCH branch per option. Field parameters do the same job without code: a slicer lets readers swap which field sits on an axis or in a value well, and Power BI generates the table for you. Choose Modeling > New parameter > Fields and tick the fields. The result is a calculated table built with NAMEOF:

DAX
Metric Fields =
{
    ("Revenue", NAMEOF('Sales'[Revenue]), 0),
    ("Profit",  NAMEOF('Sales'[Profit]),  1),
    ("Units",   NAMEOF('Sales'[Units]),   2)
}

Each row holds a display name, a reference to the field and a sort order. Drop the parameter column onto a chart's axis or values and the slicer rewires the visual. Field parameters can swap columns on an axis as well as measures, which the SWITCH pattern cannot do. The trade-off is control: with SWITCH you can add custom logic and per-branch formatting, while field parameters are quick and let report authors add options in the interface. Choose SWITCH when you need logic, and field parameters when you only need to choose between existing fields.

Choosing a pattern

All four patterns follow one recipe: capture the current context in variables, change the context deliberately (ALLSELECTED, REMOVEFILTERS, a disconnected slicer) and let SWITCH or arithmetic turn the result into the answer. When a new requirement arrives, ask which filters the number should respect and which it should ignore, and the right pattern usually follows. If a measure returns something unexpected, the next lesson, Debugging and Optimizing DAX, shows how to inspect it.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is the table that feeds a measure-selector slicer usually left without relationships?

    If the table were related to the facts, picking a value would filter the facts. Left disconnected, the selection only filters the table itself, and SELECTEDVALUE reads it as an input.
  2. 2. What does SELECTEDVALUE(Metric[Metric], "Revenue") return when the user has selected nothing?

    SELECTEDVALUE returns the single selected value, or the alternate result when zero or several values are selected.
  3. 3. In the ABC class measure, why is ALLSELECTED used instead of ALL?

    ALLSELECTED removes the visual's own row filters but keeps the outer filters, so the split recalculates when the reader filters by region or year.
  4. 4. How does the New Customers measure find a customer's first order ever?

    The first order must be found outside the current period. CALCULATE with REMOVEFILTERS('Date') widens the date context, and context transition narrows it to the current customer.
  5. 5. When are field parameters a better fit than a SWITCH selector?

    Field parameters are generated from the fields you tick and can swap columns on an axis. SWITCH is better when you need per-option logic.

Summary

  • A disconnected table plus SELECTEDVALUE and SWITCH turns a slicer into an input for a measure.
  • ABC classification divides items by cumulative share of sales (here 80% and 95%), using variables, ALLSELECTED, FILTER and SUMX.
  • New customers have their first order inside the period; finding it needs REMOVEFILTERS on the date table, and returning is active minus new.
  • Field parameters let readers swap fields on an axis or in values with no DAX, while SWITCH gives full control.
  • In every pattern, decide which filters to keep and which to ignore, then capture values in variables.

Next: Debugging and Optimizing DAX.