DAX Logical and Conditional Functions
Logical and conditional functions help Power BI evaluate conditions and return different results depending on the data. They are useful for sales classifications, performance ratings, target evaluation, discount rules, and business decision-making.
In this lesson, you will learn how to use IF, SWITCH, AND, OR, and NOT in DAX formulas. You will also practice combining conditions to create meaningful business categories and report measures.
Learning Objectives
- Understand logical conditions in DAX.
- Use IF to return results based on a condition.
- Use SWITCH to evaluate multiple cases.
- Combine conditions using AND and OR.
- Reverse a logical condition using NOT.
- Apply conditional logic to sales and performance analysis.
- Identify common errors in conditional formulas.
1. What Are Logical and Conditional Functions?
A logical expression evaluates a condition and produces a TRUE or FALSE result. A conditional function uses that result to determine which value or expression should be returned.
For example, a business might classify sales above 10,000 as "High Sales" and all other sales as "Regular Sales".
2. IF Function
The IF function checks a condition and returns one value when the condition is TRUE and another value when it is FALSE.
Syntax
IF(LogicalTest, ValueIfTrue, ValueIfFalse)
Example: Sales Classification
Sales Status =
IF(
Sales[SalesAmount] >= 10000,
"High Sales",
"Regular Sales"
)
This calculated column classifies each sales row according to its SalesAmount value.
| SalesAmount | Condition | Sales Status |
|---|---|---|
| 15,000 | 15,000 is greater than or equal to 10,000. | High Sales |
| 8,000 | 8,000 is less than 10,000. | Regular Sales |
| 10,000 | The condition is TRUE at the threshold. | High Sales |
3. Nested IF Conditions
A nested IF places one IF function inside another. This allows you to classify values into more than two groups.
Example: Sales Performance Categories
Sales Category =
IF(
Sales[SalesAmount] >= 20000,
"Excellent",
IF(
Sales[SalesAmount] >= 10000,
"Good",
"Needs Improvement"
)
)
| Sales Amount | Sales Category |
|---|---|
| 25,000 | Excellent |
| 15,000 | Good |
| 7,000 | Needs Improvement |
4. SWITCH Function
SWITCH is useful when one expression needs to be compared with several possible values. It can make a formula easier to read than a long series of nested IF functions.
Syntax
SWITCH(
Expression,
Value1, Result1,
Value2, Result2,
DefaultResult
)
Example: Product Category Code
Category Label =
SWITCH(
Products[CategoryCode],
1, "Electronics",
2, "Accessories",
3, "Furniture",
"Other"
)
If CategoryCode is 1, the result is Electronics. If the code is 2, the result is Accessories. If no listed value matches, the formula returns Other.
SWITCH with TRUE
SWITCH(TRUE()) is useful when you need to evaluate multiple conditions or ranges.
Sales Performance =
SWITCH(
TRUE(),
[Total Sales] >= 100000, "Excellent",
[Total Sales] >= 50000, "Good",
[Total Sales] >= 20000, "Average",
"Low"
)
This measure classifies the aggregated Total Sales value according to the current filter context. The conditions are evaluated in order, so the highest threshold is checked first.
5. AND Function
AND returns TRUE only when both conditions are TRUE. It is useful when a record must satisfy two requirements at the same time.
Example: High-Value Sales
High Value Sale =
IF(
AND(
Sales[SalesAmount] >= 10000,
Sales[Quantity] >= 2
),
"Qualified",
"Not Qualified"
)
A sales row is classified as Qualified only if its sales amount is at least 10,000 and its quantity is at least 2.
6. OR Function
OR returns TRUE when at least one of its conditions is TRUE. It is useful when a record can qualify through more than one business rule.
Example: Sales Alert
Sales Alert =
IF(
OR(
Sales[SalesAmount] >= 50000,
Sales[Quantity] >= 10
),
"Review",
"Normal"
)
A row receives the Review label if its sales amount is at least 50,000 or its quantity is at least 10. Both conditions do not need to be true.
7. NOT Function
NOT reverses a logical result. TRUE becomes FALSE, and FALSE becomes TRUE. It can be used when a business rule requires checking that a condition is not satisfied.
Example: Exclude Cancelled Orders
Order Status Check =
IF(
NOT(Sales[Status] = "Cancelled"),
"Include",
"Exclude"
)
The formula returns Include when the status is not Cancelled and Exclude when the status is Cancelled.
8. Combine Conditions in a Single Formula
Business rules often involve multiple requirements. You can combine logical functions to create more specific classifications.
Example: Eligible for a Discount
Discount Eligibility =
IF(
AND(
Sales[SalesAmount] >= 20000,
OR(
Sales[CustomerType] = "Premium",
Sales[Quantity] >= 5
)
),
"Eligible",
"Not Eligible"
)
This formula returns Eligible only when the sales amount is at least 20,000 and at least one of these additional conditions is true: the customer is Premium or the quantity is at least 5.
9. Comparison of Logical Functions
| Function | Purpose | Typical Use |
|---|---|---|
| IF | Returns one result when a condition is true and another when false. | Classifying sales as High or Regular. |
| SWITCH | Matches an expression against multiple values or evaluates ordered conditions with SWITCH(TRUE()). | Assigning performance labels. |
| AND | Returns TRUE when all supplied conditions are TRUE. | Checking multiple eligibility rules. |
| OR | Returns TRUE when at least one supplied condition is TRUE. | Checking alternative qualification rules. |
| NOT | Reverses a logical result. | Checking that an order is not cancelled. |
10. Common Errors and Best Practices
| Problem | Cause | Recommended Solution |
|---|---|---|
| Incorrect category returned | Conditions are evaluated in the wrong order. | Check the condition sequence, especially in nested IF and SWITCH(TRUE()). |
| Text comparison fails | The column contains different labels, unexpected spaces, or inconsistent values. | Validate the source data and standardize text values. |
| Unexpected result in a measure | The formula uses an aggregated value in the current filter context. | Test the measure in a simple visual and verify the intended context. |
| Too many nested IF functions | The expression is difficult to read and maintain. | Consider SWITCH(TRUE()) for multiple ordered conditions. |
| Blank values produce unexpected categories | The formula does not explicitly handle blank or missing data. | Consider whether BLANK() requires a separate condition. |
11. Practical Exercise
Create a Sales table using the sample records below and practice conditional calculations.
| SaleID | Product | SalesAmount | Quantity | Status |
|---|---|---|---|---|
| 1 | Laptop | 50,000 | 2 | Completed |
| 2 | Mouse | 1,500 | 3 | Completed |
| 3 | Keyboard | 2,500 | 6 | Pending |
| 4 | Monitor | 15,000 | 1 | Cancelled |
Tasks
- Create a Sales Status column using IF to classify sales of 10,000 or more as High Sales.
- Create a Sales Category column using nested IF or SWITCH(TRUE()).
- Create a High Quantity flag using a quantity threshold of 5.
- Create a Sales Alert column using OR to flag sales of 20,000 or more or quantities of 6 or more.
- Create an Order Status Check column using NOT to identify non-cancelled rows.
- Display the new columns in a table visual.
- Verify each classification against the original data.
12. Knowledge Check
Question 1
Which function returns different values based on a condition?
Answer: IF.
Question 2
Which function is useful for mapping several values to labels?
Answer: SWITCH.
Question 3
When does AND return TRUE?
Answer: When all supplied conditions are TRUE.
Question 4
When does OR return TRUE?
Answer: When at least one supplied condition is TRUE.
Question 5
What does NOT do?
Answer: It reverses a logical result.
Summary
In this lesson, you learned how DAX logical and conditional functions evaluate business rules and return different results. You practiced IF, nested IF, SWITCH, SWITCH(TRUE()), AND, OR, and NOT using sales classification and eligibility examples.
You also explored how to combine conditions, compare logical functions, and troubleshoot incorrect classifications. Always check the order of conditions, verify source values, and test conditional formulas against sample records before using them in business reports.