Daily DAX : Day 333 SWITCH

DAX SWITCH Function

Purpose

The SWITCH function in DAX (Data Analysis Expressions) evaluates an expression against a list of values and returns a result corresponding to the first matching value. It is similar to a CASE statement in SQL or a nested IF statement, but more concise and readable.

Syntax

SWITCH(<expression>, <value1>, <result1> [, <value2>, <result2>] ... [, <default_result>])
    

• <expression>: The value or expression to evaluate.
• <value1>, <value2>, ...: The values to compare against the expression.
• <result1>, <result2>, ...: The results returned if the corresponding value matches.
• <default_result> (optional): The result if no values match.

Use Case

A common use case for SWITCH is to categorize data based on a condition, such as assigning labels to ranges of values. For example, in a sales report, you might want to classify sales amounts into performance categories.

Example: Classifying Sales Performance

Suppose you have a table with a column Sales[Amount]. You want to create a new calculated column to categorize sales as "Low", "Medium", or "High" based on the amount.

Performance = 
SWITCH(
    TRUE(),
    Sales[Amount] < 1000, "Low",
    Sales[Amount] < 5000, "Medium",
    Sales[Amount] >= 5000, "High",
    "Unknown"
)
        

Explanation:

• TRUE() allows logical conditions in the SWITCH function.
• If Sales[Amount] is less than 1000, returns "Low".
• If Sales[Amount] is less than 5000, returns "Medium".
• If Sales[Amount] is 5000 or more, returns "High".
• If no conditions are met, returns "Unknown".

Result: The new column will label each row’s sales amount as "Low", "Medium", "High", or "Unknown".

Key Benefits

• Improves readability compared to nested IF statements.
• Handles multiple conditions efficiently.
• Supports a default result for unmatched cases.