DAX Operators

Data Analysis Expressions (DAX) uses a variety of operators to perform calculations and comparisons within your formulas. These operators are essential for building complex DAX expressions that can analyze your data effectively.

Types of DAX Operators

DAX operators can be categorized as follows:

1. Arithmetic Operators

Used for performing mathematical operations.

Operator Description Example
+ Addition [Sales] + [Cost]
- Subtraction [Revenue] - [Expenses]
* Multiplication [Price] * [Quantity]
/ Division [Total Sales] / [Number of Orders]
^ Exponentiation 2 ^ 3 (evaluates to 8)

2. Comparison Operators

Used for comparing two values. They return either TRUE or FALSE.

Operator Description Example
= Equal to [Country] = "USA"
> Greater than [Sales] > 1000
< Less than [Quantity] < 50
>= Greater than or equal to [Rating] >= 4
<= Less than or equal to [Age] <= 18
<> Not equal to [Status] <> "Completed"
Note: DAX comparison operators treat blank values differently than typical SQL. A blank value is treated as 0 in numeric comparisons and as an empty string ("") in text comparisons.

3. Text Concatenation Operator

Used for joining two text strings.

Operator Description Example
& Concatenation "Sales_" & [Year] (e.g., "Sales_2023")

4. Logical Operators

Used to combine multiple expressions or to evaluate conditions.

Operator Description Example
AND Returns TRUE if both expressions are TRUE. IF([Sales] > 1000 AND [Region] = "North", "High", "Low")
OR Returns TRUE if at least one expression is TRUE. IF([Status] = "Open" OR [Status] = "Pending", "Active", "Closed")
NOT Reverses the logical value of an expression. IF(NOT([IsActive]), "Inactive", "Active")

5. Operator Precedence

When multiple operators are used in a DAX expression, they are evaluated according to a specific order of precedence. Expressions within parentheses are evaluated first. Operators with higher precedence are evaluated before operators with lower precedence.

The general order of precedence is:

  1. Parentheses ()
  2. Function calls
  3. Arithmetic operators (^, *, /, +, -)
  4. Text concatenation &
  5. Comparison operators ( >, <, >=, <=, =, <> )
  6. Logical operators NOT
  7. Logical operators AND
  8. Logical operators OR
Tip: Always use parentheses to explicitly define the order of operations in complex expressions to ensure clarity and prevent unexpected results.

6. Other Operators

Member Operators

These operators are used within functions that expect a set of members.

Operator Description Example Function
: Range operator. Creates a set of members between two specified members. CALCULATE(SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], "2023-01-01", "2023-12-31"))

Parentheses

While not strictly an operator, parentheses () are crucial for grouping expressions, dictating evaluation order, and defining function arguments.

-- Example using parentheses for order of operations
SUMX(
    Products,
    Products[Price] * Products[Quantity] - Products[Discount]
)
Important: Understanding DAX operators is fundamental to writing accurate and efficient DAX formulas. Always refer to the DAX function reference for detailed information on how specific functions interact with operators.

By mastering these operators, you can unlock the full power of DAX for your data analysis needs.