Power Pivot Principles: Variables in DAX
22 January 2019
Welcome back to our Power Pivot blog. Today, we discuss how to use variables in DAX.
This week we wanted to introduce the VAR function, seeking inspiration we turned to the Microsoft help page: https://docs.microsoft.com/en-us/dax/var-dax.
What the VAR function does is (quoted directly from the Microsoft help page):
‘Stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.’
The VAR function essentially allows us to store an expression as a named variable and then call upon that same variable again later in the measure… sounds neat.
Let’s try creating a measure that calculates the year on year growth of sales:
=VAR
VarSales =
SUM(
Sales[SalesAmount]
)
VAR
SalesLastYear =
CALCULATE(
VarSales,
SAMEPERIODLASTYEAR('Calendar'[Date])
)
RETURN
IF(
VarSales,
DIVIDE(
(VarSales - SalesLastYear),
VarSales
),
0
)
The first VAR function defines the total sales, and the second VAR function determines the total sales for the ‘same period last year’. The RETURN function defines what we want the measure to spit out as the result; the difference between VarSales and SalesLastYear divided by VarSales.
Strangely, this happens when we create a PivotTable with this measure:
Weird… what’s going on? Our formula looks fairly similar to the one presented on the Microsoft help page:
= VAR VarSales = SUM(SalesTable[SalesAmount])
VAR SalesLastYear=CALCULATE(VarSales, SAMEPERIODLASTYEAR('Calendar'[Date]))
RETURN
if(Sales, DIVIDE(Sales – SalesLastYear, Sales))
Looking back at the description of how the VAR function works (again quoted directly from the Microsoft help page):
‘Stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.’
What is happening is the second VAR expression:
VAR SalesLastYear=CALCULATE(VarSales,
SAMEPERIODLASTYEAR('Calendar'[Date]))
is treating the ‘VarSales’ as a static variable, in other words a ‘paste-special-as-values’. Therefore, filtering these values with a SAMEPERIODLASTYEAR function will not yield the results we want.
A simple work around:
=VAR
VarSales =
SUM(
Sales[SalesAmount]
)
VAR
SalesLastYear =
CALCULATE(
SUM(
Sales[SalesAmount]),
SAMEPERIODLASTYEAR('Calendar'[Date])
)
RETURN
IF(
VarSales,
DIVIDE(
(VarSales - SalesLastYear),
VarSales
),
0
)
The fix involved altering the second VAR function to:
VAR
SalesLastYear =
CALCULATE(
SUM(
Sales[SalesAmount]),
SAMEPERIODLASTYEAR('Calendar'[Date])
)
This allows the measure to properly filter the total sales amount and give us the proper year on year % result:
That’s it for this week, happy pivoting!
Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
