SWITCH function in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to return 'High' when Sales is greater than 1000, otherwise 'Low'.

Power BI

SalesCategory = SWITCH(TRUE(), Sales > [1], "High", "Low")

Drag options to blanks, or click blank then click option'

A1000

B2000

C1500

D500

Attempts:

3 left

💡 Hint

Common Mistakes

Using a number less than 1000 changes the category incorrectly.

Forgetting to use TRUE() as the first argument.

The SWITCH function checks conditions in order. Using TRUE() allows logical expressions. Here, Sales > 1000 returns 'High', else 'Low'.

2fill in blank

medium

Complete the code to categorize scores: 90+ as 'Excellent', 70+ as 'Good', else 'Average'.

Power BI

ScoreCategory = SWITCH(TRUE(), Score >= [1], "Excellent", Score >= 70, "Good", "Average")

Drag options to blanks, or click blank then click option'

A100

B90

C60

D80

Attempts:

3 left

💡 Hint

Common Mistakes

Using 80 instead of 90 causes wrong categorization.

Placing conditions in wrong order.

The first condition checks if Score is 90 or more for 'Excellent'. The order matters to assign correct categories.

3fill in blank

hard

Fix the error in the SWITCH function to correctly assign 'Low', 'Medium', or 'High' based on Quantity.

Power BI

QuantityLevel = SWITCH(TRUE(), Quantity <= 10, "Low", Quantity <= [1], "Medium", "High")

Drag options to blanks, or click blank then click option'

A15

B5

C20

D25

Attempts:

3 left

💡 Hint

Common Mistakes

Using a number less than or equal to 10 causes overlap.

Using a number too high skips the medium category.

The medium level should cover quantities up to 20. Using 20 ensures correct ranges without overlap.

4fill in blank

hard

Fill both blanks to categorize Age into 'Child' (<=12), 'Teen' (<=19), or 'Adult'.

Power BI

AgeGroup = SWITCH(TRUE(), Age <= [1], "Child", Age <= [2], "Teen", "Adult")

Drag options to blanks, or click blank then click option'

A12

B18

C19

D20

Attempts:

3 left

💡 Hint

Common Mistakes

Using 18 instead of 19 excludes 19-year-olds from 'Teen'.

Swapping the blanks reverses categories.

Children are 12 or younger, teens up to 19, adults above. The blanks must reflect these age limits.

5fill in blank

hard

Fill the blanks to assign product categories: 'Electronics' for code 1, 'Clothing' for 2, else 'Other'.

Power BI

ProductCategory = SWITCH(ProductCode, [1], "Electronics", [2], "Clothing", "Other")

Drag options to blanks, or click blank then click option'

A1

B2

C3

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong codes swaps categories.

Not including a default value causes errors.

SWITCH matches ProductCode to 1='Electronics', 2='Clothing', else defaults to 'Other'.

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