Variables in DAX
The VAR keyword stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions.
VAR keyword returns a variable containing the result of the expression argument.
Syntax:
VAR <VARNAME1> = <EXPRESSION1>
VAR <VARNAME2> = <EXPRESSION2> [...]]
RETURN <RESULT_EXPRESSION>
- Description of the parameter:
|
PARAMETER |
DESCRIPTION |
|---|---|
|
varname |
The name of your variable. Supported character set: a-z, A-Z, 0-9. Delimiters are not supported 0-9 are not valid as first character |
|
expression |
DAX expression which returns a scalar or table value |
Example:
We want to calculate the percentage of year-over-year growth using the formula:
SalesAmount YoY% = ([Sum of SalesAmount] – [SalesAmount PreviousYear]) / [Sum of SalesAmount]
We could create three different measures ([Sum of SalesAmount], [SalesAmount PreviousYear] et [Sum of SalesAmount]) and then apply the previous formula to get the final result.
By using variables, you can get the same outcome, but in a more readable way. In addition, the result of the expression doesn’t have to be recalculated each time it is used.
The following formula uses the variables to calculate the percentage of year-over-year growth.
SalesAmount YoY% =
var SalesAmount = SUM(Sales[SalesAmount])
var SalesAmountLastYear = CALCULATE( SUM(Sales[SalesAmount]),
SAMEPERIODLASTYEAR('Calendar'[Date])
)
return
IF(SalesAmount,
DIVIDE(SalesAmount - SalesAmountLastYear, SalesAmount)
)
Note that:
An expression passed as an argument to VAR can contain another VAR declaration.
Variables can refer to measures.
Variables can refer to previously defined variables.
Related Video:
Related Articles: