Use DAX operators

Completed 100 XP

Your DAX formulas can use operators to create expressions that perform arithmetic calculations, compare values, work with strings, or test conditions.

Tip

Many DAX operators and precedence order are the same as those found in Excel.

Arithmetic operators

The following table lists the arithmetic operators.

Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
^ Exponentiation

Remember, when you are dividing two expressions, and when the denominator could return zero or BLANK, it's more efficient and safer to use the DIVIDE DAX function.

Comparison operators

The following table lists the comparison operators, which are used to compare two values. The result is either TRUE or FALSE.

Operator Description
= Equal to
== Strict equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to

All comparison operators, except strict equal to (==), treat BLANK as equal to the number zero, an empty string (""), the date December 30, 1899, or FALSE. It means that the expression [Revenue] = 0 will be TRUE when the value of [Revenue] is either zero or BLANK. In contrast, [Revenue] == 0 is TRUE only when the value of [Revenue] is zero.

Text concatenation operator

Use the ampersand (&) character to connect, or concatenate, two text values to produce one continuous text value. For example, consider the following calculated column definition:

Model Color = 'Product'[Model] & "-" & 'Product'[Color]

Logical operators

Use logical operators to combine expressions that produce a single result. The following table lists all logical operators.

Operator Description
&& Creates an AND condition between two expressions where each has a Boolean result. If both expressions return TRUE, the combination of the expressions also returns TRUE; otherwise the combination returns FALSE.
|| (double pipe) Creates an OR condition between two logical expressions. If either expression returns TRUE, the result is TRUE; only when both expressions are FALSE is the result FALSE.
IN Creates a logical OR condition between each row that is being compared to a table. Note: The table constructor syntax uses braces.
NOT Inverts the state of a Boolean expression (FALSE to TRUE, and vice versa).

An example that uses the IN logical operator is the ANZ Revenue measure definition, which uses the CALCULATE DAX function to enforce a specific filter of two countries: Australia and New Zealand.

Note

You'll be introduced to the powerful CALCULATE function when you learn how to modify the filter context.

ANZ Revenue =
CALCULATE(
    [Revenue],
    Customer[Country-Region] IN {
        "Australia",
        "New Zealand"
    }
)

Operator precedence

When your DAX formula includes multiple operators, DAX uses rules to determine the evaluation order, which is known as an operator precedence. Operations are ordered according to the following table.

Operator Description
^ Exponentiation
- Sign (as in -1)
* and / Multiplication and division
NOT NOT
+ and - Addition and subtraction
& Concatenation of two strings of text
=,==,<,>,<=,>=,<> Comparison

When the operators have equal precedence value, they're ordered from left to right.

In general, operator precedence is the same as what's found in Excel. If you need to override the evaluation order, then group operations within parentheses.

For example, consider the following calculated column definition:

Extended Amount = Sales[Order Quantity] * Sales[Unit Price] * 1 - [Unit Price Discount Pct]

This sample calculated column definition produces an incorrect result because multiplication happens before the subtraction. The following correct calculated column definition uses parentheses to ensure that the subtractions happen before the multiplications.

Extended Amount = Sales[Order Quantity] * Sales[Unit Price] * (1 - [Unit Price Discount Pct])

Tip

Remembering operator precedence rules can be challenging, especially for DAX beginners. Consequently, we recommend that you test your formulas thoroughly. When the formulas don't produce the correct result due to an incorrect evaluation order, you can experiment by adding parentheses to adjust the evaluation order. You can also add parentheses to improve the readability of your formulas.

For more information about DAX operators and precedence order, see DAX operators.

Implicit conversion

When writing a DAX formula that uses operators to combine different data types, you don't need to explicitly convert types. Usually, DAX automatically identifies the data types of referenced model objects and performs implicit conversions where necessary to complete the specified operation.

However, some limitations might exist on the values that can be successfully converted. If a value or a column has a data type that's incompatible with the current operation, DAX returns an error. For example, the attempt to multiply a date value will create an error because it isn't logical.

BLANK is handled differently, depending on the operator that is used. It's handled similar to how Excel treats BLANK, but differently to how databases (SQL) treat NULL. BLANK is treated as zero when acted on by arithmetic operators and as an empty string when concatenated to a string.

Tip

Remembering how BLANK is handled can be challenging, especially for DAX beginners. Consequently, we recommend that you test your formulas thoroughly. When BLANKs create unexpected results, consider using the IF and ISBLANK DAX functions to test for BLANK, and then respond in an appropriate way.


Next unit: Use DAX variables

Previous Next