There are loads of great new features in today’s release of Power BI Desktop, but for me the most important by far is the introduction of calculated tables. Miguel Llopis gives a good introduction to what they are in the post announcing the release, but I thought it was worth going into a bit more detail about what they are and why they’re so useful.
What are calculated tables?
Calculated tables are tables in the Data Model whose data source is a DAX expression that returns a table. Here’s a simple example. Imagine that you have already imported the DimDate dimension table from the Adventure Works DW database into your Power BI Data Model. If you go to the Data tab you would see the contents of that table shown, and on the ribbon you can see the new New Table button:
Clicking the button allows you to enter a new table name and a DAX expression that returns the table, such as this one that returns a filtered subset of the rows in the DimDate table:
[sourcecode language=”text” padlinenumbers=”true”]
DimDate[CalendarYear] = 2002
&& DimDate[EnglishMonthName] = "June"
&& DimDate[DayNumberOfMonth] < 5
Calculated tables are created when the data in the model is refreshed (like calculated columns), and after that behave like any other table – so you can create relationships between calculated tables and other tables. You can also create calculated tables whose DAX expressions reference other calculated tables. They do take up memory like other tables too, so over-using them could be a bad thing.
Why are calculated tables useful?
Miguel’s blog post already lists some of the scenarios where calculated tables are useful, and I can already think of lots of practical scenarios where I’m going to be using them myself.
Role playing dimensions are one obvious use: in a lot of models you need to use the same dimension table more than once in different places, with different relationships and maybe with different filters in place. It might be that you have a single Company dimension in your data warehouse that contains all of the companies your organisation does business with; with calculated tables you only need to import that table once, and you can then use calculated tables to create filtered copies of that table to use as Supplier and Customer dimension tables, joining them to your Stock and Sales fact tables, and only showing the relevant companies in each case.
Certainly for debugging complex DAX expressions they’re going to be handy, because they allow you to see what DAX table expressions return. We’ve already DAX Studio for that but now we don’t have the hassle of switching to another application…!
I can also see calculated tables as a way of doing certain types of ETL – which raises the question of whether you should do a certain operation in Get Data (ie what was Power Query) or using a calculated table. I strongly suspect that a lot of operations are going to be much faster with calculated tables because of the power of the underlying engine. It would be interesting to know if there are plans to allow Get Data to make use of calculated tables, for example as a way of buffering tables in memory, with M transformations folded back to DAX on those tables.
The Calendar() and CalendarAuto() functions
If you were wondering what the new DAX Calendar() and CalendarAuto() functions were for, well, you can probably guess now – Date tables. The Calendar() function returns a table of continuous dates within a given range. So, the expression
CALENDAR ( "1/1/2015", "2/2/2015" )
Will return a table with one column containing all the dates from January 1st 2015 to February 2nd 2015:
The CalendarAuto() function looks at all of the Date columns in all of the other tables in the model, and returns a similar table but one where the first date is the beginning of the year that contains the earliest date found in any non-calculated column in any non-calculated table, and where the last date is the end of the year that contains the latest date found in any non-calculated column in any non-calculated table. By default the beginning of the year is January 1st and the end of the year is December 31st, but there’s also an optional parameter to specify a different month to end the year on, if you want to create a fiscal calendar table.
I wonder if we’ll get something like the functionality in Excel 2016 for creating date tables with other useful columns?