How to do a CASE Statement in DAX : Combine SWITCH with TRUE
In Power BI DAX (Data Analysis Expressions), the SWITCH function combined with TRUE() is a powerful technique used to evaluate multiple conditions and return a result based on the first condition that evaluates to true. This approach mimics the behavior of nested IF statements but is more readable and efficient, especially for complex logic.
Syntax of SWITCH
dax
SWITCH(expression, value1, result1, [value2, result2, ...], [default_result])
When using SWITCH(TRUE(), ...):
The expression is replaced with TRUE().
The function evaluates a series of logical conditions (as value1, value2, etc.) in order.
The first condition that evaluates to TRUE returns its corresponding result.
If no condition is true, the default_result (if provided) is returned.
Syntax with TRUE()
dax
SWITCH(
TRUE(),
condition1, result1,
condition2, result2,
...
[default_result]
)
condition1, condition2, ...: Logical expressions that evaluate to TRUE or FALSE.
result1, result2, ...: The values or expressions returned when the corresponding condition is true.
default_result (optional): The value returned if no conditions are true.
How It Works
The SWITCH(TRUE()) pattern evaluates conditions sequentially, stopping at the first TRUE condition and returning its associated result. This is particularly useful for categorizing data, creating custom calculations, or applying conditional logic in measures or calculated columns.
Use Case
Scenario: A retail company wants to categorize sales amounts into performance tiers based on the following rules:
Sales ≥ 10,000: "Excellent"
Sales ≥ 5,000: "Good"
Sales ≥ 1,000: "Average"
Otherwise: "Poor"
DAX Formula (as a calculated column or measure):
dax
SalesTier =
SWITCH(
TRUE(),
Sales[Amount] >= 10000, "Excellent",
Sales[Amount] >= 5000, "Good",
Sales[Amount] >= 1000, "Average",
"Poor"
)
Result:
For a sale of 12,000: Returns "Excellent".
For a sale of 6,500: Returns "Good".
For a sale of 2,000: Returns "Average".
For a sale of 500: Returns "Poor".
Common Applications
Data Categorization: Assign categories or labels based on ranges or conditions (e.g., customer segments, product tiers).
Conditional Calculations: Apply different calculations based on specific criteria (e.g., discount rates, tax rates).
Dynamic Formatting: Create measures for conditional formatting in visuals (e.g., color-coding based on performance).
Simplifying Complex Logic: Replace multiple nested IF statements for better readability and maintenance.
Example in Power BI
Scenario: A company tracks employee performance scores (0–100) and wants to assign grades:
Score ≥ 90: "A"
Score ≥ 80: "B"
Score ≥ 70: "C"
Score ≥ 60: "D"
Otherwise: "F"
DAX Formula (calculated column):
dax
Grade =
SWITCH(
TRUE(),
Employees[Score] >= 90, "A",
Employees[Score] >= 80, "B",
Employees[Score] >= 70, "C",
Employees[Score] >= 60, "D",
"F"
)
Output:
Score of 95: "A"
Score of 85: "B"
Score of 65: "D"
Score of 50: "F"
Advantages of SWITCH(TRUE())
Readability: More concise and easier to read than nested IF statements.
Maintainability: Simplifies updating or adding conditions.
Performance: Generally performs better than deeply nested IFs in large datasets.
Notes
Order Matters: Conditions are evaluated sequentially, so place more specific conditions first to avoid overlap (e.g., >= 10000 before >= 5000).
Default Result: Always include a default result to handle cases where no conditions are true, preventing blank or unexpected outputs.
Limitations: Conditions must evaluate to TRUE or FALSE. For non-boolean comparisons, use the standard SWITCH syntax (without TRUE()).
Comparison to Nested IF
The above grade example with nested IFs would look like:
dax
Grade =
IF(Employees[Score] >= 90, "A",
IF(Employees[Score] >= 80, "B",
IF(Employees[Score] >= 70, "C",
IF(Employees[Score] >= 60, "D", "F")
)
)
)
This is harder to read and maintain compared to SWITCH(TRUE()).
How it Differs to Standard SWITCH function
In Power BI DAX, the SWITCH function can be used in two primary ways: SWITCH(expression, value1, result1, ...) and SWITCH(TRUE(), condition1, result1, ...). The key difference between using SWITCH with a specific expression (standard SWITCH) and SWITCH(TRUE()) lies in how conditions are evaluated and the type of logic they support. Below, I’ll explain the differences, their use cases, and when to use each.
1. Standard SWITCH
The standard SWITCH function evaluates a single expression and compares its result against a set of predefined values, returning the corresponding result for the first matching value.
Syntax
dax
SWITCH(expression, value1, result1, [value2, result2, ...], [default_result])
expression: A scalar expression (e.g., a column value, measure, or calculation) to evaluate.
value1, value2, ...: Specific values to compare the expression against.
result1, result2, ...: The results returned when the expression matches the corresponding value.
default_result (optional): The value returned if no match is found.
How It Works
The expression is evaluated once.
The result is compared against each value in the order listed.
The first matching value triggers its corresponding result.
If no match is found, the default_result is returned (if provided).
Example
Scenario: A company categorizes products based on their exact product code.
DAX Formula (calculated column):
dax
ProductCategory =
SWITCH(
Products[Code],
"A1", "Electronics",
"B2", "Clothing",
"C3", "Furniture",
"Unknown"
)
Result:
Code "A1" → "Electronics"
Code "B2" → "Clothing"
Code "C3" → "Furniture"
Code "D4" → "Unknown"
Use Case
Best for scenarios where you need to map specific, discrete values (e.g., codes, categories, or statuses) to corresponding results.
Common in lookup scenarios, such as translating codes or IDs into meaningful labels.
2. SWITCH(TRUE())
The SWITCH(TRUE()) approach uses the SWITCH function with TRUE() as the expression, allowing you to evaluate a series of logical conditions (not just exact matches) and return the result for the first condition that evaluates to TRUE.
Syntax
dax
SWITCH(
TRUE(),
condition1, result1,
condition2, result2,
...
[default_result]
)
TRUE(): A constant expression that is always true, enabling the evaluation of conditions.
condition1, condition2, ...: Logical expressions (e.g., Sales[Amount] > 1000) that evaluate to TRUE or FALSE.
result1, result2, ...: The results returned when the corresponding condition is true.
default_result (optional): The value returned if no conditions are true.
How It Works
The function evaluates each condition in sequence.
The first condition that evaluates to TRUE triggers its corresponding result.
If no condition is true, the default_result is returned (if provided).
Example
Scenario: Categorize sales amounts into performance tiers.
DAX Formula (calculated column):
dax
SalesTier =
SWITCH(
TRUE(),
Sales[Amount] >= 10000, "Excellent",
Sales[Amount] >= 5000, " "Excellent" returns "Excellent" returns "Good" returns "Average" returns "Poor" returns "Poor"
**Result**:
- Sales of 12,000 → "Excellent"
- Sales of 6,500 → "Good"
- Sales of 2,000 → "Average"
- Sales of 500 → "Poor"
#### Use Case
- Ideal for **conditional logic** where you need to evaluate **ranges, comparisons, or complex conditions** (e.g., greater than, less than, or combinations of conditions).
- Used for categorizing data, applying business rules, or creating dynamic calculations.
---
### Key Differences
| **Aspect** | **Standard SWITCH** | **SWITCH(TRUE())** |
|---------------------------|------------------------------------------------|------------------------------------------------|
| **Expression** | Evaluates a single scalar expression (e.g., column or measure). | Uses `TRUE()` to evaluate logical conditions. |
| **Comparison Type** | Matches exact values (e.g., "A1", 1, "Red"). | Evaluates logical conditions (e.g., `>`, `<`, `&&`). |
| **Flexibility** | Limited to exact matches. | Highly flexible for complex or range-based logic. |
| **Use Case** | Mapping discrete values to results (e.g., lookup tables). | Conditional logic, ranges, or multi-criteria rules. |
| **Readability** | Simple for straightforward mappings. | Cleaner than nested IFs for complex conditions. |
| **Performance** | Slightly faster for simple value matching. | May be slightly slower for complex conditions but negligible in most cases. |
---
### When to Use Each
- **Use Standard SWITCH**:
- When you have a **fixed set of known values** to map to specific results (e.g., product codes, status IDs, or category names).
- For simple, direct value-to-result mappings.
- Example: Translating region codes ("NA", "EU", "APAC") to full names.
- **Use SWITCH(TRUE())**:
- When you need to evaluate **conditions or ranges** (e.g., sales thresholds, score ranges, or multi-condition logic).
- For complex business rules or categorizations based on comparisons.
- Example: Assigning grades based on score ranges or applying tiered discounts.
---
### Combined Example
**Scenario**: A company has a table with a column `StatusCode` (e.g., 1, 2, 3) and a column `SalesAmount`. You want to create a calculated column that categorizes records based on both status and sales amount.
**DAX Formula**:
```dax
Category =
SWITCH(
TRUE(),
AND(Orders[StatusCode] = 1, Orders[SalesAmount] > 10000), "VIP",
Orders[StatusCode] = 1, "Standard",
Orders[StatusCode] = 2, "Pending",
Orders[StatusCode] = 3, "Cancelled",
"Unknown"
)
Explanation:
Uses SWITCH(TRUE()) to evaluate conditions like AND for status and sales amount.
If StatusCode is 1 and SalesAmount > 10,000, it returns "VIP".
If only StatusCode is 1, it returns "Standard".
For other status codes, it maps directly to "Pending" or "Cancelled".
Falls back to "Unknown" if no conditions match.
For a standard SWITCH, you might only map StatusCode:
dax
StatusDescription =
SWITCH(
Orders[StatusCode],
1, "Active",
2, "Pending",
3, "Cancelled",
"Unknown"
)
Notes
Order Matters: In both versions, the order of conditions/values is critical. The first match wins, so place more specific conditions/values first.
Default Result: Always include a default result to handle unexpected cases and avoid blanks.
Performance: For very large datasets, test performance, as complex SWITCH(TRUE()) with many conditions might be slightly slower than optimized alternatives (though usually negligible).
Comments
Post a Comment