---
title: DAX - SWITCH function
tags: dax
---

# `SWITCH` function

Core concept: Use disconnected tables

// Create a disconnected table using DAX
Switch Table = 
DATATABLE(
    "measure", STRING,
    {
        {"Revenue"},
        {"Cost"}
    }
)

## Case 1: Conditional Columns

**Requirement**: Create a calculated column to classify customer by age group.

Customer Age Group = 
VAR customer_age = `Customer`[Age]
RETURN
SWITCH(
    TRUE(),
    customer_age < 18, "1. < 18",
    customer_age <= 30, "2. 19-30",
    customer_age <= 45, "3. 31-45",
    customer_age <= 65, "4. 45-65",
    customer_age > 65, "5. > 65"
)

<details>
<summary>A more dynamic methods</summary>

Create the following calculated table:

Age Group = 
DATATABLE (
    "Group", STRING,
    "Lower bound", INTEGER,
    "Upper bound", INTEGER,
    {
        { "1. < 18", 0, 18 },
        { "2. 18 - 30", 18, 30 },
        { "3. 30 - 45", 30, 45 },
        { "4. 45 - 65", 45, 65},
        { "5. 65++", 65, 200}
    }
)

Create the following calculate column in Customer table:

Age Group = 
CALCULATE (
    VALUES ( 'Age Group'[Group] ),
    FILTER (
        'Age Group',
        Customer[Age] >= 'Age Group'[Lower bound]
            && Customer[Age] < 'Age Group'[Upper bound]
    )
)

</details>

## Case 2: Switch Measures

**Switch True Logic**: You run through some calculations and evaluate them as true. If they’re true, they will return a result.

### Basic practice

Step 1: Create measures
- Revenue = Total revenue
- Cost = Total production cost

Step 2: Create a disconnected table
- Name: Switch Table
- Data: Column names "Measure" contains two values: "Revenue" and "Cost"

Step 3: Create a switch measure

Revenue or Cost = 
var selected_measure = SELECTEDVALUE('Switch Table'[Measure], "Revenue")
return
SWITCH(
    TRUE(),
    selected_measure = "Revenue", [Revenue],
    selected_measure = "Cost", [Cost])

Step 4: Test with visual

### Another practice

(shorter but not that dynamic, still use disconnected table)

SelectedMeasure =
IF(
    HASONEVALUE(MeasuresTable[Measures]),
    SWITCH(
        VALUES(MeasuresTable[Measures]),
        “Sales”, SUM(Orders[Sales]),
        “Profit”, SUM(Orders[Profit]),
        “Customers”, DISTINCTCOUNT(Orders[Customer Name])
    ),
    SUM(Orders[Sales])
)

### Dynamic title

Measure Title =
    IF(
        HASONEVALUE(MeasuresTable[Measures]),
        VALUES(MeasuresTable[Measures]),
        “Sales”
    )
& ” by Category”

**More interesting techniques:**

- [DAX and Conditional Formatting together](https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column) combine with [Write conditional statement using SWITCH in DAX](https://radacad.com/write-conditional-statement-using-switch-in-dax-and-power-bi)
- [Multiple Measure Slicer](https://www.fourmoo.com/2019/02/19/do-you-know-how-to-use-multiple-disconnected-slicers-in-power-bi/)