The VAR function stores the result of an expression as a variable, being possible to pass it as an argument to other expressions that define a measure.
VAR name = expression
- name: Variable name:
- It does not support delimiters before or after the name. For example, myVariable is valid, but "myVariable" (including quotes) or [myVariable] (including square brackets) are not.
- Supports the characters a-z, A-Z, 0-9.
- The first character cannot be a number.
- The use of a double underscore (__) as a prefix is supported, but no other special characters are allowed.
- The use of reserved keywords is not allowed.
- The use of existing table names is not allowed.
- The use of blank spaces is not allowed.
- expression: DAX expression that returns a scalar or table.
The VAR function returns the name of a variable containing the result of evaluating the indicated expression.
Once the expression to be stored in the variable has been calculated, its result is not modified, even if the variable is referenced in another expression.
The expression used as an argument can contain the declaration of other variables.
- Measures cannot refer to variables defined outside the expression of the measure itself, but can refer to functional scoop variables defined within the expression.
- Variables can refer to measures.
- Variables can refer to previously defined variables.
- Columns in table-type variables cannot be referenced using the TableName[ColumnName] syntax.
The use of variables can lead to an improvement in the performance of the measure when the expression that defines the variable is used more than once in the definition of the measure: instead of being calculated as many times as it appears, it is only calculated once, leaving its result available to be used as many times as necessary without consuming more resources.
To calculate the percentage of growth in sales year over year, we can create three separate measures:
Sales = SUM(Sales[SalesAmount])
...measure that calculates total sales, assuming Sales is the sales table and SalesAmount the field containing the amount of each sale.
Sales Previous Year = CALCULATE([Sales], SAMEPERIODLASTYEAR(Calendar[Date]))
...measure that calculates sales for the same period of the previous year assuming that Calendar is the table with the calendar, and:
Increment = DIVIDE([Sales] - [Sales Previous Year], [Sales])
Using variables we can write the same code in a single measure:
Increment =
VAR Sales = SUM(Sales[SalesAmount])
VAR SalesYearPrevious = CALCULATE(Sales, SAMEPERIODLASTYEAR('Calendar'[Date]))
RETURN DIVIDE(Sales - SalesYearPrevious, Sales)
