SWITCH function in Power BI - Cell-by-Cell Formula Trace

Sample Data

This table lists products and their category codes.

Cell	Value
A1	Product
A2	Apple
A3	Banana
A4	Cherry
B1	Category Code
B2	1
B3	2
B4	3

Formula Trace

SWITCH(B2, 1, "Fruit", 2, "Snack", 3, "Dessert", "Unknown")

Step 1: SWITCH(1, 1, "Fruit", 2, "Snack", 3, "Dessert", "Unknown")

Cell Reference Map

    A       B
1 Product  Category Code
2 Apple    -> 1
3 Banana     2
4 Cherry     3

The formula uses the value in cell B2 (Category Code for Apple) to decide the output.

Result

    A       B          C
1 Product  Category Code  Category Name
2 Apple    1          Fruit
3 Banana   2          
4 Cherry   3          

The SWITCH formula in C2 returns "Fruit" because B2 is 1, matching the first case.

Sheet Trace Quiz - 3 Questions

What does the SWITCH function return when B2 is 1?

A "Fruit"

B "Snack"

C "Dessert"

D "Unknown"

Key Result

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

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