`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"
)

A more dynamic methods

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]
    )
)

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:

SWITCH function

Case 1: Conditional Columns

Case 2: Switch Measures

Basic practice

Another practice

Dynamic title

Read more

DS Assignment

Introduction to DAX - Part 3 (Revision)

Introduction to DAX - Part 2 (Revision)

Introduction to DAX - Part 1 (Revision)

`SWITCH` function