Logical functions in DAX are used to apply conditions in calculations. They help you create rules, categorize data, and build dynamic measures based on specific criteria.
The two most commonly used logical functions in DAX are IF and SWITCH.
IF Function
The IF function checks a condition and returns one value if the condition is TRUE and another value if it is FALSE.
Syntax
IF(logical_test, value_if_true, value_if_false)
Example 1 – Basic Condition
Profit Status = IF(Sales[Profit] > 0, "Profit", "Loss")
This calculated column checks if Profit is greater than 0.
If TRUE, it returns “Profit”; otherwise, it returns “Loss”.
Example 2 – Measure with IF
High Sales = IF(SUM(Sales[Amount]) > 100000, "Target Achieved", "Below Target")
This measure dynamically evaluates total sales based on filter context.
When to Use IF
- Categorizing data (Pass/Fail, Profit/Loss)
- Creating performance indicators
- Applying simple conditional logic
SWITCH Function
The SWITCH function is used when you need to evaluate multiple conditions. It is cleaner and more readable than using multiple nested IF statements.
Syntax
SWITCH(expression, value1, result1, value2, result2, ..., else_result)
Example 1 – Category Mapping
Sales Category =
SWITCH(
TRUE(),
Sales[Amount] >= 100000, "High",
Sales[Amount] >= 50000, "Medium",
Sales[Amount] >= 10000, "Low",
"Very Low"
)
Here, SWITCH(TRUE()) allows you to evaluate multiple logical conditions.
Example 2 – Based on Exact Match
Region Name =
SWITCH(
Sales[RegionCode],
1, "North",
2, "South",
3, "East",
4, "West",
"Unknown"
)
When to Use SWITCH
- When handling multiple conditions
- Replacing nested IF statements
- Mapping codes to readable labels
IF vs SWITCH
IF is best for simple two-condition logic.
SWITCH is better for handling multiple conditions and improves readability.
Using SWITCH makes your DAX cleaner and easier to maintain.
Conclusion
Logical functions like IF and SWITCH allow you to apply decision-making logic in your data model. They are essential for categorization, KPI evaluation, and building intelligent Power BI reports.