SWITCH function in Power BI - Step-by-Step Guide

Introduction

The SWITCH function helps you pick one result from many options based on a value. It solves the problem of writing many IF statements by making your formulas simpler and easier to read.

When you want to assign categories based on a product type code.

When you need to show different messages depending on sales ranges.

When you want to create a new column that labels customers by region codes.

When you want to replace multiple nested IFs with a cleaner formula.

When you want to return different results for specific values in a column.

Steps

Step 1: Click

- Modeling tab

The ribbon shows options to create new measures and columns

Step 2: Click

- New column

A formula bar appears to write a DAX expression

Step 3: Type

- formula bar

The formula bar shows your typed SWITCH function

💡 Start with: SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, ..., <else_result>)

Step 4: Press Enter

- formula bar

The new column is created and appears in the data table with results based on your SWITCH logic

Step 5: Use

- Report view

Drag the new column into a visual to see the categorized data

Before vs After

Before

A table with a ProductType column containing codes like 1, 2, 3

After

A new column shows 'Fruit' for 1, 'Vegetable' for 2, 'Other' for any other code

Settings Reference

Expression

📍 First argument in SWITCH function

Defines the value to compare against multiple cases

Default: None

Value-result pairs

📍 Arguments after the expression in SWITCH

Defines what result to return for each matching value

Default: None

Else result

📍 Last argument in SWITCH function (optional)

Result returned if no values match

Default: BLANK() if omitted

Common Mistakes

Forgetting to add the else result argument

If no match is found, the function returns blank, which may confuse users

Always add a final else result to handle unmatched cases

Using SWITCH without an expression (trying to use it like nested IF)

SWITCH requires an expression to compare; without it, syntax is incorrect

Use SWITCH with an expression or use SWITCH(TRUE(), ...) pattern carefully

Summary

SWITCH simplifies choosing results based on one value compared to many options.

It reduces the need for many nested IF statements, making formulas easier to read.

Remember to include an else result to handle unexpected values.

Practice

1. What does the SWITCH function do in Power BI?

easy

A. It filters rows based on a condition.

B. It creates a new table from existing data.

C. It chooses a result based on matching values or conditions.

D. It sums values in a column.

Solution

1. Step 1: Understand the purpose of SWITCH

   The SWITCH function evaluates an expression and returns a result based on matching values or conditions.

2. Step 2: Compare with other functions

   Unlike filtering or summing, SWITCH simplifies multiple IF conditions into one formula.

3. Final Answer:

   It chooses a result based on matching values or conditions. -> Option C

4. Quick Check:

   SWITCH picks result by matching values [OK]

Hint: Remember: SWITCH picks output based on matching input [OK]

Common Mistakes:

• Confusing SWITCH with filtering functions
• Thinking SWITCH creates tables
• Assuming SWITCH sums values

2. Which of the following is the correct syntax for a basic SWITCH function in DAX?

easy

A. SWITCH(<expression>, <value1>, <value2>, <result1>, <result2>, <else_result>)

B. SWITCH(<value1>, <result1>, <value2>, <result2>, <else_result>)

C. SWITCH(<expression>, <result1>, <value1>, <result2>, <value2>, <else_result>)

D. SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, <else_result>)

Solution

1. Step 1: Recall SWITCH syntax

   The correct syntax is SWITCH(expression, value1, result1, value2, result2, else_result).

2. Step 2: Check option correctness

   SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, <else_result>) matches the correct order: expression first, then pairs of value and result, ending with else result.

3. Final Answer:

   SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, <else_result>) -> Option D

4. Quick Check:

   Correct SWITCH syntax = SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, <else_result>) [OK]

Hint: Expression first, then value-result pairs, else last [OK]

Common Mistakes:

• Omitting the expression argument
• Swapping values and results
• Placing else_result in the middle

3. Given the DAX measure:

Category Result = SWITCH(
  'Sales'[Category],
  "A", "High",
  "B", "Medium",
  "C", "Low",
  "Unknown"
)

What is the result when 'Sales'[Category] is "B"?

medium

A. "High"

B. "Medium"

C. "Low"

D. "Unknown"

Solution

1. Step 1: Identify the input value

   The input value is 'Sales'[Category] = "B".

2. Step 2: Match input with SWITCH cases

   SWITCH checks "A" -> "High", "B" -> "Medium", "C" -> "Low". Since input is "B", result is "Medium".

3. Final Answer:

   "Medium" -> Option B

4. Quick Check:

   Category "B" returns "Medium" [OK]

Hint: Match input value to cases in order [OK]

Common Mistakes:

• Choosing the first result always
• Confusing value and result pairs
• Ignoring the else result

4. Identify the error in this DAX formula:

Result = SWITCH(
  TRUE(),
  Sales[Amount] > 1000, "High",
  Sales[Amount] > 500, "Medium",
  Sales[Amount] > 100, "Low"
)

medium

A. Missing ELSE result for unmatched conditions.

B. Incorrect use of TRUE() in SWITCH.

C. Conditions should be separated by commas, not line breaks.

D. Sales[Amount] cannot be compared with numbers.

Solution

1. Step 1: Analyze SWITCH with TRUE()

   Using TRUE() allows SWITCH to evaluate conditions in order.

2. Step 2: Check for ELSE result

   No ELSE (default) result is provided, so if none match, result is blank or error.

3. Final Answer:

   Missing ELSE result for unmatched conditions. -> Option A

4. Quick Check:

   Always add ELSE result in SWITCH with TRUE() [OK]

Hint: Add ELSE result to cover all cases [OK]

Common Mistakes:

• Forgetting ELSE result causes blanks
• Thinking TRUE() is invalid in SWITCH
• Misunderstanding condition order

5. You want to create a measure that categorizes sales amount into "Very High" (above 2000), "High" (1001-2000), "Medium" (501-1000), "Low" (1-500), and "No Sales" (0 or less). Which SWITCH formula correctly implements this?

hard

A. SWITCH(TRUE(), Sales[Amount] > 2000, "Very High", Sales[Amount] > 1000, "High", Sales[Amount] > 500, "Medium", Sales[Amount] > 0, "Low", "No Sales")

B. SWITCH(Sales[Amount], >2000, "Very High", >1000, "High", >500, "Medium", >0, "Low", "No Sales")

C. SWITCH(TRUE(), Sales[Amount] >= 2000, "Very High", Sales[Amount] >= 1000, "High", Sales[Amount] >= 500, "Medium", Sales[Amount] >= 1, "Low", "No Sales")

D. SWITCH(TRUE(), Sales[Amount] > 2000, "Very High", Sales[Amount] >= 1000, "High", Sales[Amount] >= 501, "Medium", Sales[Amount] >= 1, "Low", "No Sales")

Solution

1. Step 1: Use SWITCH with TRUE() for condition checks

   Using SWITCH(TRUE(), ...) allows checking conditions in order from highest to lowest.

2. Step 2: Verify condition ranges and order

   SWITCH(TRUE(), Sales[Amount] > 2000, "Very High", Sales[Amount] > 1000, "High", Sales[Amount] > 500, "Medium", Sales[Amount] > 0, "Low", "No Sales") correctly uses > 2000, > 1000, > 500, > 0, then else "No Sales". This covers all ranges without overlap.

3. Step 3: Check other options for errors

   SWITCH(Sales[Amount], >2000, "Very High", >1000, "High", >500, "Medium", >0, "Low", "No Sales") has invalid syntax (cannot use >2000 as value). Options A and D use >= which changes boundaries and may overlap or miss exact ranges.

4. Final Answer:

   SWITCH(TRUE(), Sales[Amount] > 2000, "Very High", Sales[Amount] > 1000, "High", Sales[Amount] > 500, "Medium", Sales[Amount] > 0, "Low", "No Sales") -> Option A

5. Quick Check:

   Use SWITCH(TRUE()) with descending conditions [OK]

Hint: Order conditions from highest to lowest with TRUE() [OK]

Common Mistakes:

• Using invalid syntax for conditions
• Overlapping or missing ranges
• Not using TRUE() for condition checks