SWITCH TRUE in DAX: Segmentation, and the Band That Never Appears

1. What nested IF costs you

Before the comparison: here is a three-band segmentation written with IF. Read it and say how confident you are that the thresholds are what you think they are.

Order Size =
IF (
    Orders[Revenue] < 500,
    "Small",
    IF (
        Orders[Revenue] < 800,
        "Medium",
        "Large"
    )
)

It is correct, and it is already hard to read at three bands. At five bands it is five levels of indentation, the closing brackets pile up, and adding a band in the middle means re-nesting everything below it. The logic is fine; the shape is the problem.

SWITCH exists for this. Microsoft's reference describes it as evaluating "an expression against a list of values" and returning one of several results, and says plainly that it "can be used to avoid having multiple nested IF statements". Same logic, flat.

There is a real cost to the nesting beyond readability. A nested IF hides the ordering of your conditions inside its structure, so the question "which condition is tested first" requires reading inwards. A SWITCH puts them in a list, top to bottom, and the order becomes something you can check by scanning down the left edge. Section four is why that matters.

2. SWITCH on a value, the simple form

Before the syntax: you want to turn a month number into a month name. Predict how many arguments that takes, before reading on.

The plain form of SWITCH takes an expression and then pairs of value and result, with an optional final catch-all.

SWITCH ( <expression>, <value>, <result> [, <value>, <result> ] … [, <else> ] )

Product Line =
SWITCH (
    Products[Product],
    "Desk",  "Furniture",
    "Chair", "Furniture",
    "Lamp",  "Lighting",
    "Unclassified"
)

Read it as a lookup: take the product, find it in the list, return what is beside it. The last argument on its own, with no value before it, is the else: what to return when nothing matched. Leave it out and Microsoft's reference is specific about what happens: "If none of the values match and else isn't specified, BLANK is returned."

This form only does equality. It compares the expression to each value and looks for an exact match, so it cannot express "greater than 500". For that you need the form in the next section, which is the one you will use most.

A note on where this particular example belongs. Mapping products to categories is better done as a column on the Products table than as DAX at all, for the reasons in the star schema guide. SWITCH is for logic that genuinely belongs in the model, not for a lookup table someone has not built yet.

3. SWITCH TRUE, for conditions instead of values

Before the trick: you want bands based on ranges, not exact matches. The first argument is an expression to compare against. Guess what you could put there to make comparisons work, before reading on.

Put TRUE(). Then each "value" position holds a condition, which evaluates to true or false, and SWITCH compares that to TRUE. The first condition that is true wins. Microsoft's reference names this pattern directly: "A common use of this function is to set the first parameter to TRUE."

Order Size =
SWITCH (
    TRUE (),
    Orders[Revenue] < 500, "Small",
    Orders[Revenue] < 800, "Medium",
    "Large"
)

Flat, readable top to bottom, and each line says its own condition and its own answer. Adding a fourth band is one more line in the right place rather than another level of nesting.

Here is what it produces on our sixteen orders, as a calculated column, grouped up.

Two checks worth running on any segmentation, and they take one glance. The counts add to 16, the row count you started with, so no order was dropped. And the revenue adds to 9,890, so no order was counted twice. A segmentation that fails either check has a gap or an overlap in its conditions.

Say out loud why the "Medium" band's condition is written as < 800 rather than >= 500 && < 800. It is because anything under 500 was already caught by the line above and never reaches this one. That is the whole economy of the pattern, and it is also exactly what makes the next section possible.

4. The ordering bug, on real numbers

Before the bug: swap the first two lines of that measure, so < 800 is tested before < 500. Predict what happens to the Small band, before reading on.

It gets nothing. Not an error, not a warning: zero rows, forever.

Order Size Broken =
SWITCH (
    TRUE (),
    Orders[Revenue] < 800, "Medium",     -- catches everything under 800
    Orders[Revenue] < 500, "Small",      -- unreachable
    "Large"
)

Look at the bottom row of the broken column. Sixteen orders, 9,890 in revenue. The reconciliation still passes. Nothing is missing and nothing is double-counted; the orders are simply in the wrong buckets. Both of the checks from the last section are satisfied by a segmentation that is completely wrong, which is exactly why this bug survives review.

Microsoft's reference states the rule and gives the same warning: "The order of conditions matters. As soon as one value matches, the corresponding result is returned, and other subsequent values aren't evaluated. Make sure the most restrictive values to be evaluated are specified before less restrictive values." The documentation's own example is the same shape as ours, with a note that its second result "is never returned".

The check that catches it is not a reconciliation. It is one extra column: the smallest and largest actual value in each band.

Now the bands can be read against their own definitions. Small tops out at 480, under the 500 threshold. Medium runs 510 to 765, inside its window. Large starts at 850. Run this once when you write a segmentation and the ordering bug cannot survive it.

Now picture your own longest SWITCH or nested IF. Scan its conditions from the top. Is each one narrower than the one below it, or did somebody add a band later without moving it?

5. Column or measure: where a segment lives

Before the decision: you want to slice a report by order size. Predict whether the SWITCH should be a calculated column or a measure, before reading on.

A calculated column, and the reason is structural rather than a preference. Only a column can go on an axis, in a slicer, or on a legend, because those places need actual values stored in the model to build a list from. A measure produces one number inside whatever filters are already applied; it cannot supply the categories that would do the filtering.

The distinction is the same one covered from the other side in columns vs measures, and it decides itself with one question: does the answer depend on what the viewer has filtered? Our order size does not. An order of 440 is small whatever the viewer clicks, so it is a column.

Here is a segment that genuinely has to be a measure, because it depends on a filtered total rather than on one row.

Rep Tier =
SWITCH (
    TRUE (),
    [Total Revenue] >= 3000, "Top",
    [Total Revenue] >= 2000, "Middle",
    "Building"
)

Read the total row, because it is the thing that catches people. With no rep filter, [Total Revenue] is 9,890, which is over 3,000, so the measure says "Top". That is arithmetically correct and it is meaningless as a label for the whole company. Any measure that returns a category needs its total row thought about, and often suppressed with a check like IF ( HASONEVALUE ( Reps[Rep] ), <the switch> ).

6. Banding on a measure, and the disconnected table

Before the problem: you want a bar chart with order-size bands on the axis, where the bands are based on a measure rather than a stored column. Say why the measure from the last section will not go on the axis.

Because a measure has nothing to put there. The axis needs a list of values, and a measure returns one value per cell, after the axis already exists. This is the standard wall people hit, and the standard solution has a name.

Build a small table of the bands themselves, with no relationship to anything. It is usually called a disconnected table, and it exists purely to supply values to an axis or a slicer.

Bands =
DATATABLE (
    "Band",  STRING,
    "Sort",  INTEGER,
    "Min",   INTEGER,
    "Max",   INTEGER,
    {
        { "Small",  1,   0,  499 },
        { "Medium", 2, 500,  799 },
        { "Large",  3, 800, 99999 }
    }
)

Put Bands[Band] on the axis, sorted by Bands[Sort], then write a measure that counts the orders falling inside whichever band's row it is currently sitting in.

Orders in Band =
VAR Lo = MIN ( Bands[Min] )
VAR Hi = MAX ( Bands[Max] )
RETURN
    CALCULATE (
        COUNTROWS ( Orders ),
        FILTER ( ALL ( Orders[Revenue] ),
                 Orders[Revenue] >= Lo && Orders[Revenue] <= Hi )
    )
-- 6, 6, 4

Two things make that work. The band table has no relationship, so its rows do not filter Orders on their own, and MIN and MAX read the current row's thresholds out of it. And ALL(Orders[Revenue]) clears any existing revenue filter first, so each band counts against the whole column rather than against whatever the axis already restricted. That is the CALCULATE machinery from the filter context guide doing exactly what it was built for.

The payoff is that the thresholds now live in a table rather than in code. Change 500 to 600 in the band table and every visual updates, with no measure edited. Put the band table's rows in a slicer and the user can even pick their own.

Do not reach for this unless you need it. If the value being banded is a stored column that never changes, a calculated column is simpler, faster and easier for the next person to read.

7. Data types, blanks, and the else branch

Before the rules: you write a SWITCH where one result is a string and another is a number. Predict whether it runs.

It does not. Microsoft's reference is unambiguous: "All result expressions and the else expression must be of the same data type", and the page shows exactly this error, a SWITCH returning "Large" in one branch and 0.1 in another. It is a common accident when somebody adds a numeric fallback to a text segmentation.

Three more behaviours worth knowing before they cost you an afternoon.

No match and no else gives BLANK. Usually not what you want on a segmentation, because blank-labelled rows are hidden by many visuals and the orders inside them disappear from the report while still counting in the grand total. Always write an else, even if it says "Unclassified", because an explicit bucket you can see beats a silent one you cannot.

A blank input matches the wrong band. In DAX a blank compares as zero in a numeric comparison, so an order with no revenue satisfies < 500 and lands in Small. If blank means "unknown" rather than "zero", test for it first: put ISBLANK(Orders[Revenue]), "Unknown" as the very first condition, where its narrowness earns it the top spot.

Conditions can combine. Each condition is an ordinary boolean expression, so && and || work: Orders[Revenue] >= 800 && RELATED ( Products[Category] ) = "Furniture", "Large furniture". Just remember that a compound condition is usually more restrictive, so it belongs above the simpler ones.

Edge cases that catch people out

Six that each cost somebody an afternoon.

Adding a band later without moving it. This is how the ordering bug actually happens in real models. Nobody writes the conditions backwards; somebody appends a narrower band at the bottom six months later. Re-read the whole list every time you add to it.

Overlapping thresholds. <= 500 then >= 500 puts 500 in the first band only, which is defensible and often not what was meant. Write bands as "under X" all the way down, so the boundaries cannot overlap by construction.

Bands sorting alphabetically. Large, Medium, Small on the axis. Add a numeric sort column and set Sort by column, exactly as with month names.

Hard-coded thresholds in several measures. When 500 appears in four measures, changing it means finding all four. Either put it in the band table from section six, or in a single variable, and never in more than one place.

SWITCH over a measure with no HASONEVALUE guard. The total row evaluates the measure with no filter, which almost always lands in the top band and labels the whole company "Top".

Thresholds picked out of the air. 500 and 800 are round numbers, not findings. If the bands are meant to mean something, derive them from the distribution, which is what choosing thresholds from the data and percentiles are for.