DAX now has variable support!

With the latest release of the Power BI designer that now supports measure creation we also snuck in another feature that is very useful in complicated measure scenario’s and for performance optimizations. DAX now supports variables. Lets take a look at what that means.

Here is an example that I use in my Power BI designer sessions where I calculated the future value of a principle amount, this is something that is very commonly used in the stock market. Excel has a function for this called FVSCHEDULE. Using our new PRODUCTX function it is pretty straightforward to implement yourself.

Lets take this example where I want to see what would happen when we apply a set of compound interest rates to the sales of a promotion. I loaded a simple table that gives me the rates I want to apply that I subsequently added as slicer:

Ok now for the calculations:

First I create a measure that compounds the interest rates using PRODUCTX:

Rates calc = PRODUCTX(Rates,1+[Rates])

This calculation will return the Product of 1 + [Rates] for each row in the Rate table.

Now when I want to use it I actually create another measure:

Future Investment = IF([Sum of SalesAmount], [Sum of SalesAmount] * [Rates calc])

This calculation will multiply the Sum of SalesAmount by the Rates Calc.

Together giving these results:

Ok now lets take a look at rewriting this using variables, in general the syntax to use variables is the following:

Measure name =
var varname = DAX formula

var varname2 = DAX formula

return varname + varname2

now writing the formula we created before using variables you get the following:

Future value variable =

var Ratescalc = Productx(Rates,1+[Rates])
var Revenue = [Sum of SalesAmount]
return if (Revenue, Revenue * Ratescalc )

Adding it to the visual:

Ok lets look at another example, variables can not just take single values, you can also use them to store tables:

testvar = var table1 = FILTER(Customer, [Sum of SalesAmount] > 20)
var table2 = FILTER(Customer , Customer[AgeGroup] = “1 < 25”)
var tableunion = UNION(table1,table2)
return COUNTROWS(tableunion)

Ok lets recap, why do you want to use variables?

1. They can improve readability of your measures
2. They can improve performance as measure values get stored into a variable and can be reused in other places without having to calculate the value several times.

I can write this YoY% measure:

SalesAmount PreviousYear=CALCULATE([Sum of SalesAmount], SAMEPERIODLASTYEAR(Calendar[Date]))

 

Sum of SalesAmount YoY%:=if([Sum of SalesAmount] ,
DIVIDE(([Sum of SalesAmount] – [SalesAmount PreviousYear]), [Sum of SalesAmount]))

using variables into:

YoY% = var Sales = [Sum of SalesAmount]
var SalesLastYear=CALCULATE([Sum of SalesAmount], SAMEPERIODLASTYEAR(‘Calendar'[Date]))
return if(Sales, DIVIDE(Sales – SalesLastYear, Sales))

First of all it is more readable (of course this is a matter of opinion :)) but second of all the [Sum of SalesAmount] measure is calculated 4 times if you also count the previous year measure. In the variable case [Sum of SalesAmount] is only executed twice. Now in this example it doesn’t really make a big difference but if your measure get more and more complicated this can really make a difference.

Ok enough for now 🙂 Go download the Power BI designer