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 formulavar 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?
- They can improve readability of your measures
- 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
Very interesting…do you know if this will be included in SSAS Tabular 2016?
it probably will be
Kasper, I am trying to get a DISTINCTCOUNT across a column in two tables. Similar to how you do COUNTROWS(tableunion), but when I attempt to use the DISTINCTCOUNT(tableunion[column]) I get this error “Table variable xxxxx cannot be used in current context because a base table is expected”. Anyway around this error?
Also: Would this functionality be included in Office 2016?
good question, let me look into it.
Thanks Kasper, that worked like a charm. For my understanding, is there a way to know which functions insist on having a “base table” (like DISTINCTCOUNT did) whereas which functions may be okay with using a Defined Variable Table? Short of just trying them out 🙂
Kasper,
Where the Sum of SalesAmount comes from?
Oded Dror
Oded Dror
That is another simple measure that summarized the salesamount column that is already defined in the model.
Kasper,
Is there anyway you can provide the dataset? (assuming contoso?)
Thanks,
Oded Dror