SWITCH function in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Output of SWITCH with numeric expression

What is the result of this DAX measure?

Measure = SWITCH(2, 1, "One", 2, "Two", 3, "Three", "Other")

Power BI

Measure = SWITCH(2, 1, "One", 2, "Two", 3, "Three", "Other")

A "One"

B "Three"

C "Two"

D "Other"

💡 Hint

SWITCH compares the first argument to each value in pairs.

The SWITCH function compares the first argument (2) to each value (1, 2, 3). It matches 2, so returns "Two".

🎯 Scenario

intermediate

Using SWITCH for category labels

You have a sales table with a column 'RegionCode' containing values 1, 2, or 3. You want a measure that returns "North" for 1, "South" for 2, "East" for 3, and "Unknown" otherwise.

Which DAX expression correctly implements this?

A RegionName = SWITCH(TRUE(), [RegionCode] = "1", "North", [RegionCode] = "2", "South", [RegionCode] = "3", "East", "Unknown")

B RegionName = SWITCH(TRUE(), [RegionCode] = 1, "North", [RegionCode] = 2, "South", [RegionCode] = 3, "East", "Unknown")

C RegionName = SWITCH([RegionCode], "1", "North", "2", "South", "3", "East", "Unknown")

D RegionName = SWITCH([RegionCode], 1, "North", 2, "South", 3, "East", "Unknown")

💡 Hint

Match the numeric RegionCode directly without TRUE() when comparing values.

Option D correctly uses SWITCH with the expression [RegionCode] and matches numeric values 1, 2, 3. Options C and D compare to strings which won't match numeric codes. Option D uses SWITCH(TRUE()) but compares numeric values without quotes, which is valid but less direct.

🔧 Formula Fix

advanced

Identify error in SWITCH with missing default

What error occurs when running this DAX measure?

Measure = SWITCH([Score], 1, "Low", 2, "Medium", 3, "High")

Assume [Score] can be 4.

Power BI

Measure = SWITCH([Score], 1, "Low", 2, "Medium", 3, "High")

A Returns blank for [Score] = 4

B Returns "High" for [Score] = 4

C Syntax error due to missing default value

D Runtime error due to unmatched value

💡 Hint

SWITCH returns blank if no match and no default is provided.

When no match is found and no default value is specified, SWITCH returns blank (empty) instead of an error.

❓ visualization

advanced

Best visualization for SWITCH-based category measure

You created a measure using SWITCH to categorize customers into "New", "Active", and "Churned" based on purchase dates.

Which visualization best shows the count of customers per category clearly and accessibly?

A Stacked bar chart with categories stacked vertically

B Pie chart with distinct colors and labels for each category

C Line chart showing categories over time

D Scatter plot with categories as point colors

💡 Hint

Think about showing parts of a whole clearly with labels.

A pie chart clearly shows proportions of categories with distinct colors and labels, making it easy to understand customer distribution.

🧠 Conceptual

expert

SWITCH vs Nested IF performance and readability

Which statement best explains the advantage of using SWITCH over nested IF statements in DAX for multiple conditions?

A SWITCH improves readability and can be more efficient because it evaluates the expression once and matches values, unlike nested IF which evaluates each condition separately.

B Nested IF is always faster because it stops evaluating as soon as a condition is true, while SWITCH evaluates all conditions.

C SWITCH and nested IF have identical performance and readability in all cases.

D SWITCH can only handle numeric comparisons, so nested IF is preferred for text.

💡 Hint

Consider how many times the expression is evaluated in SWITCH vs IF.

SWITCH evaluates the expression once and compares it to multiple values, improving readability and often performance. Nested IF evaluates each condition separately, which can be less efficient and harder to read.

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