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:
- 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 operator | Meaning | Example |
|---|---|---|
| + (plus sign) | Addition | 3+3 |
| – (minus sign) | Subtraction or sign | 3–1–1 |
| * (asterisk) | Multiplication | 3*3 |
| / (forward slash) | Division | 3/3 |
| ^ (caret) | Exponentiation | 16^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 operator | Meaning | Example |
|---|---|---|
| = | 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 operator | Meaning | Example |
|---|---|---|
| & (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 operator | Meaning | Examples |
|---|---|---|
| && (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")) |
| IN | Creates 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.
| Order | Operator | Description |
|---|---|---|
| 1 | ^ | Exponentiation |
| 2 | – | Sign (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.
=5+2*3In 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.
=(5+2)*3In 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).
=(3 + 0.25)/(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.
=-2^2To 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.
= (-2)^2Credit: Microsoft
