DAX - SWITCH vs SWITCH TRUE

In DAX one powerful and easy-to-use function is SWITCH(). SWITCH is a logical function that essentially replicates what you'd achieve by nesting multiple IF() statements—but in a much cleaner, more readable, and scalable way. This function evaluates the value of a given expression (typically a column or a measure), compares it against multiple specified conditions, and returns the corresponding result for the first match it finds. In practice, SWITCH() goes through each row, checks whether the conditions are met, and outputs the corresponding value based on the first match—making your logic more compact and easier to manage than a chain of nested IF() functions.

Row Context - DAX - SWITCH vs SWITCH TRUE

Now let’s see how SWITCH() behaves in a row context, such as in calculated columns or calculated tables. A common use case is when we want to create a calculated column that tells us which quarter a specific date falls into. To achieve this, we could write the following expression. SWITCH() will then evaluate each row of the Dates[Month Number] column and compare its value against the listed conditions (1, 2, 3, etc.). When a match is found, it returns the corresponding result — like "Q1", "Q2", "Q3", and so on.

Quarter Number = 
SWITCH(
    Dates[Month Number],
    1, "Q1",
    2, "Q1",
    3, "Q1",
    4, "Q2",
    5, "Q2",
    6, "Q2",
    7, "Q3",
    8, "Q3",
    9, "Q3",
    10, "Q4",
    11, "Q4",
    12, "Q4",
    "-"
    )

The result is a calculated column where each month is categorized into its respective quarter — Q1 through Q4 — depending on the value in the Month Number column.

However, unlike the IF function, SWITCH does not support direct logical comparisons like >, <, <=, etc. within its standard syntax. In fact, if we try to write something like the code below, it will return an error — because by default, SWITCH can only perform equality (=) comparisons between the expression and the specified values.

Benchmark compasiron = 
SWITCH(
    Categories[Average Price] < 1400, "Green",
    Categories[Average Price] < 1500, "Red",
    Categories[Average Price] < 1600, "Yellow",
    "Purple"
)

To work around this limitation, we can use the TRUE() function as the first argument in SWITCH(). This approach allows us to write logical conditions that return TRUE or FALSE, and then SWITCH simply matches the first condition that evaluates to TRUE, returning the corresponding result.

Let’s go through an example. Suppose we want to compare the average price of each category against different thresholds (e.g. 1400, 1500, etc.) and return a specific color based on which range it falls into. To do this, we can use SWITCH(TRUE(), ...).

Here, SWITCH will first evaluate each condition (e.g. Average Price < 1400), which returns either TRUE or FALSE. Then it compares that result with the TRUE() we’ve set as the first argument in the function — which is always TRUE, of course — and once it finds a match, it returns the corresponding value we’ve defined (e.g. "Green", "Orange", "Red", etc.). This way, we’ve transformed the logical comparison (e.g., <, >, etc.) into a boolean expression, allowing SWITCH to evaluate based on equality (=) — which is the only type of comparison it natively supports.

It’s important to note that the order in which we list the expressions for SWITCH to evaluate is crucial for obtaining the correct result. In fact, simply changing the order of the conditions can lead to different outcomes. This happens because SWITCH stops evaluating as soon as it finds the first condition that returns TRUE, and it ignores any remaining expressions. The image below demonstrates this scenario in a clear and practical way.

Filter Context

The last point I’d like to cover is a common issue when using SWITCH inside a measure. Let’s take the following formula as an example:

Benchmark comparison = 
SWITCH(
    TRUE,
    Categories[Average Price] > 700, "Greater",
    "Lower"
)

While this formula works perfectly in a calculated column, it will throw an error if used in a measure. Why? Because in a measure, DAX operates in filter context, not row context. That means you can’t directly refer to a column like Categories[Average Price] on its own — DAX doesn’t know which row you're referring to.

To make this work inside a measure, we need to wrap the column reference inside an aggregation function, such as MAX, MIN, or SELECTEDVALUE, depending on your use case. Here’s the corrected version that works in a measure:

Benchmark comparison = 
SWITCH(
    TRUE,
    MAX(Categories[Average Price]) > 700, "Greater",
    "Lower"
)

In this case, MAX() provides a scalar value that DAX can evaluate in the current filter context — allowing SWITCH to function correctly.

DAX - SWITCH vs SWITCH TRUE

---

Thanks for being a part of our community!

If you found this article helpful and would like to show your support, don’t hesitate to:

1. Clap on this story

2. Leave a comment below telling me what you think. This will help me with the next articles

3. Support my work on Buy Me a Coffee ☕️

These actions really really help me out, and are much appreciated!

Follow me for more insights on LinkedIn | YouTube | Medium