Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Stores the result of an expression as a named variable, which you can then pass as an argument to other measure expressions. After DAX calculates the values for a variable expression, those values don't change, even if you reference the variable in another expression.
Syntax
VAR <name> = <expression>
Parameters
| Term | Definition |
|---|---|
name |
The name of the variable (identifier). Delimiters aren't supported. For example, 'varName' or [varName] results in an error. Supported character set: a-z, A-Z, 0-9. 0-9 aren't valid as first character. __ (double underscore) is allowed as a prefix to the identifier name. No other special characters are supported. Reserved keywords aren't allowed. Names of existing tables aren't allowed. Empty spaces aren't allowed. |
expression |
A DAX expression that returns a scalar or table value. |
Return value
A named variable containing the result of the expression argument.
Remarks
An expression passed as an argument to VAR can contain another VAR declaration.
When you reference a variable:
- Measures can't refer to variables defined outside the measure expression, but can refer to functional scope variables defined within the expression.
- Variables can refer to measures.
- Variables can refer to previously defined variables.
- You can't reference columns in table variables by using TableName[ColumnName] syntax.
For best practices when using VAR, see Use variables to improve your DAX formulas.
To learn more about how VAR is used within a DAX Query, see DAX queries.
Example
Examples in this article can be used with the sample Adventure Works DW 2020 Power BI Desktop model. To get the model, see DAX sample model.
To calculate a percentage of year-over-year growth without using a variable, you could create three separate measures. This first measure calculates Sum of Sales Amount:
Sum of Sales Amount =
SUM ( Sales[Sales Amount] )
A second measure calculates the sales amount for the previous year:
Sales Amount PreviousYear =
CALCULATE ( [Sum of Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
You can then create a third measure that combines the other two measures to calculate a growth percentage. Notice that Sum of SalesAmount is used in two places: first to check whether a sale exists, then again to calculate a percentage.
Sum of SalesAmount YoY%: =
IF (
[Sum of Sales Amount] && [Sales Amount PreviousYear],
DIVIDE (
( [Sum of Sales Amount] - [Sales Amount PreviousYear] ),
[Sales Amount PreviousYear]
)
)
By using a variable, you can create a single measure that calculates the same result:
YoY% =
VAR Sales =
SUM ( Sales[Sales Amount] )
VAR SalesLastYear =
CALCULATE ( SUM ( Sales[Sales Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
IF ( Sales && SalesLastYear, DIVIDE ( Sales - SalesLastYear, SalesLastYear ) )
By using a variable, you can get the same outcome in a more readable way. And because the result of the expression is stored in the variable, the measure's performance improves significantly because DAX doesn't recalculate it each time it's used.