SWITCH Function in Power BI DAX with Example

The SWITCH function in DAX (Data Analysis Expressions) within Power BI is used for conditional branching. It allows you to evaluate a specified expression against a list of values and returns the result corresponding to the first matching value. If no match is found, it returns a default value.

Here’s the general syntax of the SWITCH function:

DAX

SWITCH(expression, value1, result1, value2, result2, ..., default)

• expression: The expression to evaluate.
• value1, value2, ...: The values to compare against the expression.
• result1, result2, ...: The results to return if the expression matches the corresponding value.
• default: The default result to return if no match is found.

Here’s an example to illustrate how the SWITCH function works in Power BI:

Let’s say you have a table of products with their respective sales amounts, and you want to categorize the products based on their sales amounts into three categories: “Low”, “Medium”, and “High”. You can use the SWITCH function to achieve this.

Assuming you have a table named “Products” with columns “Product” and “SalesAmount”, you can create a calculated column to categorize the products using the SWITCH function:

DAX

Category = 
SWITCH(
    TRUE(),
    Products[SalesAmount] < 1000, "Low",
    Products[SalesAmount] >= 1000 && Products[SalesAmount] < 5000, "Medium",
    Products[SalesAmount] >= 5000, "High",
    "Unknown"
)

In this example:

• If the sales amount is less than 1000, the category will be “Low”.
• If the sales amount is between 1000 (inclusive) and 5000 (exclusive), the category will be “Medium”.
• If the sales amount is 5000 or more, the category will be “High”.
• If none of the above conditions are met (which should not happen in this example), the category will be set as “Unknown”.

You can customize the conditions and categories based on your specific requirements. The SWITCH function provides a flexible way to handle conditional logic in your Power BI reports and calculations.

In the SWITCH function provided, the first argument is TRUE(). This argument serves as a condition that is always true.

Using TRUE() as the condition ensures that the SWITCH function will evaluate all subsequent conditions and return the result for the first condition that evaluates to true. If none of the conditions are true, it returns the default value (in this case, “Unknown”).

So, the TRUE() condition acts as a placeholder to initiate the evaluation process and ensure that subsequent conditions are checked. It’s a common practice to use TRUE() as the first condition in the SWITCH function when you want to evaluate multiple conditions sequentially.