What Are Logical Functions in DAX?
Logical functions in DAX are designed to perform operations based on conditions and return Boolean values (TRUE or FALSE) or other specific outputs depending on the evaluated condition. These functions are invaluable for filtering data, creating calculated columns, managing measures, and building decision trees within your Power BI reports.
Key DAX Logical Functions
Below is an overview of the most commonly used logical functions in DAX:
1. AND
The AND function evaluates two or more conditions and returns TRUE only if all the conditions are TRUE. Otherwise, it returns FALSE.
Syntax: AND(condition1, condition2)
Example: AND([Sales] > 1000, [Profit] > 500)
2. OR
The OR function checks multiple conditions and returns TRUE if at least one of the conditions is TRUE. If all conditions are FALSE, it returns FALSE.
Syntax: OR(condition1, condition2)
Example: OR([Region] = "East", [Region] = "West")
3. NOT
The NOT function negates a Boolean value, turning TRUE into FALSE and vice versa.
Syntax: NOT(condition)
Example: NOT([Inventory] > 0)
4. IF
The IF function is one of the most versatile logical functions in DAX. It evaluates a condition and returns one value if the condition is TRUE and another value if the condition is FALSE.
Syntax: IF(condition, value_if_true, value_if_false)
Example: IF([Sales] > 1000, "High", "Low")
5. SWITCH
The SWITCH function evaluates an expression against multiple values and returns a corresponding result. It is particularly useful for scenarios with multiple conditions.
Syntax: SWITCH(expression, value1, result1, [value2, result2], ... [, else_result])
Example: SWITCH([Region], "East", 1, "West", 2, 0)
6. TRUE and FALSE
The TRUE and FALSE functions are used to return Boolean values directly in formulas.
Syntax: TRUE() and FALSE()
Example: IF(TRUE(), "Active", "Inactive")
| Function | Description | Example |
| ------------ | ------------------------------------------------------------ | -------------------------------------------------------------- |
| `IF()` | Returns one value if a condition is TRUE, another if FALSE | `IF([Sales] > 1000, "High", "Low")` |
| `SWITCH()` | Evaluates an expression and matches it to a value | `SWITCH([Rating], 1, "Poor", 2, "Fair", 3, "Good", "Unknown")` |
| `AND()` | Returns TRUE if all arguments are TRUE | `IF(AND([Sales]>1000, [Profit]>100), "OK", "Check")` |
| `OR()` | Returns TRUE if any argument is TRUE | `IF(OR([Region]="West", [Sales]>5000), "Yes", "No")` |
| `NOT()` | Returns the opposite of a logical value | `IF(NOT([Active]), "Inactive", "Active")` |
| `IFERROR()` | Returns a value if there's no error, otherwise another value | `IFERROR([Revenue] / [Units], 0)` |
| `TRUE()` | Returns the logical value TRUE | `IF(TRUE(), "Always", "Never")` |
| `FALSE()` | Returns the logical value FALSE | `IF(FALSE(), "Never", "Always")` |
| `ISBLANK()` | Checks whether a value is blank (null) | `IF(ISBLANK([Date]), "No Date", [Date])` |
| `ISNUMBER()` | Checks if a value is numeric | `IF(ISNUMBER([Code]), "Numeric", "Text")` |
| `CONTAINS()` | Checks if a table contains a specific value | `CONTAINS(Products, Products[ProductID], 123)` |
watch