DAX operators

DAX Operators

The Data Analysis Expression (DAX) language uses operators to create expressions that compare values, perform arithmetic calculations, or work with strings. In this post, we will describe the use of each operator.

There are four different types of calculation operators:

  1. Arithmetic, 2. Comparison, 3. Text concatenation, and 4. Logical.

Arithmetic operators

To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic operatorMeaningExample
+ (plus sign)Addition3+3
– (minus sign)Subtraction or sign3–1–1
* (asterisk)Multiplication3*3
/ (forward slash)Division3/3
^ (caret)Exponentiation16^4

Comparison operators

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.

Comparison operatorMeaningExample
=Equal to[Region] = "USA"
>Greater than[Sales Date] > "Jan 2009"
<Less than[Sales Date] < "Jan 1 2009"
>=Greater than or equal to[Amount] >= 20000
<=Less than or equal to[Amount] <= 100
<>Not equal to[Region] <> "USA"

Text concatenation operator

Use the ampersand (&) to join, or concatenate, two or more text strings to produce a single piece of text.

Text operatorMeaningExample
& (ampersand)Connects, or concatenates, two values to produce one continuous text value[Region] & ", " & [City]

Logical operators

Use logical operators (&&) and (||) to combine expressions to produce a single result.

Text operatorMeaningExamples
&& (double ampersand)Creates an AND condition between two expressions that each have a Boolean result. If both expressions return TRUE, the combination of the expressions also returns TRUE; otherwise the combination returns FALSE.([Region] = "France") && ([BikeBuyer] = "yes"))
|| (double pipe symbol)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.(([Region] = "France") || ([BikeBuyer] = "yes"))
INCreates a logical OR condition between each row being compared to a table. Note: the table constructor syntax uses curly braces.'Product'[Color] IN { "Red", "Blue", "Black" }

Operators and precedence order

In some cases, the order in which calculation is performed can affect the Return value; therefore, it is important to understand how the order is determined and how you can change the order to obtain the desired results.

Calculation order

An expression evaluates the operators and values in a specific order. All expressions always begin with an equal sign (=). The equal sign indicates that the succeeding characters constitute an expression.

Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Expressions are always read from left to right, but the order in which the elements are grouped can be controlled to some degree by using parentheses.

Operator precedence

If you combine several operators in a single formula, the operations are ordered according to the following table. If the operators have equal precedence value, they are ordered from left to right. For example, if an expression contains both a multiplication and division operator, they are evaluated in the order that they appear in the expression, from left to right.

OrderOperatorDescription
1^Exponentiation
2Sign (as in –1)
3* and /Multiplication and division
4!NOT (unary operator)
5+ and –Addition and subtraction
6&Connects two strings of text (concatenation)
7=< ><=>=<>Comparison

Using parentheses to control calculation order

To change the order of evaluation, you should enclose in parentheses that part of the formula that must be calculated first. For example, the following formula produces 11 because multiplication is calculated before addition. The formula multiplies 2 by 3, and then adds 5 to the result.

In contrast, if you use parentheses to change the syntax, the order is changed so that 5 and 2 are added together, and the result multiplied by 3 to produce 21.

In the following example, the parentheses around the first part of the formula force the calculation to evaluate the expression (3 + 0.25) first and then divide the result by the result of the expression, (3 - 0.25).

In the following example, the exponentiation operator is applied first, according to the rules of precedence for operators, and then the sign operator is applied. The result for this expression is -4.

To ensure that the sign operator is applied to the numeric value first, you can use parentheses to control operators, as shown in the following example. The result for this expression is 4.

Credit: Microsoft