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.

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.

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?

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 🙂