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”]
MyFilteredDimDateTable =
FILTER (
DimDate,
DimDate[CalendarYear] = 2002
&& DimDate[EnglishMonthName] = "June"
&& DimDate[DayNumberOfMonth] < 5
)
[/sourcecode]
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
[sourcecode language=”text”]
CalendarDemo =
CALENDAR ( "1/1/2015", "2/2/2015" )
[/sourcecode]
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?
You hit my favorite parts of this awesome release on the nose. Thanks.
Jason Thomas raised a valid point with my privately just now, that I thought deserved a mention. He asked the obvious question of why you would use calculated tables to handle the role-playing dimension scenario I describe instead of just handling it in ETL, in views or in Get Data/Power Query. I guess that there are two reasons why you would use calculated tables rather than those methods, although they are preferable. First, you might not be in a situation where you can alter the ETL or create views; you will always be able to use Get Data though. Secondly, for some data sources loading the data can be very time-consuming. It may be faster, rather than loading three filtered copies of the table from the data source, to load it once and do the filtering inside the model with calculated tables.
As per Jason’s question. One issue is that not a single one of the small businesses I work with have a data warehouse. Therefore, for these clients, any notion of ETL is moot. As you mentioned, this new Calculated Table feature makes role-playing dimensions easy to create. For example, I often use a date table created in Excel. Now I can create Order Date, Ship Date, etc. calculated tables that narrows down a twenty-year date window to display dates containing only the years in the appropriate date columns of a fact table.
The Calendar functionality is woefully inadequate. You’re still left to create all of the date related columns you need. Instead, we need the functionality of the Date Dimension wizard in SASS. Until then, I’ll continue to use my Excel date table, since it already contains all of the calendars that can be generated in SASS’s Date Dimension wizard (Calendar, Fiscal calendar, ISO calendar, Reporting calendar, Manufacturing calendar), plus the ability to include holidays and selling seasons.
Honestly, I”m not too worried about the Calendar functionality in Excel 2016. While it would be nice, it’s pretty easy to knock up a Power Query script to do this, and with Power Query native to Excel 2016 it avoids some of the hassles with integration in Excel 2010/2013.
Hi,
I currently load a calendar dates table from the marketplace.
Is there a way to use the dates functions you mentioned in the query editor? this will save me a lot of loading time..
btw, I’m not too experienced with Power BI.
Thanks!
Eran
Hi Chris, very useful post but did you maybe highlight the wrong button in your first screenshot?
Ahhh yes, thanks for pointing that out! It’s fixed now.
Hi Chris, I created a DAX calculated table in Power BI desktop and it works and refreshes perfectly. However, when the file is published to Power BI the updates fail with “internal error”. Have you come across this at all?
No, but the current build is definitely… buggy. I’ve had a lot of problems with it and I know others have too.
Actually a team member faced the same issue, and we have raised it with the Product team. Should get resolved soon, I guess.
Appreciate the feedback from both of you and thanks for raising the issue Jason. Lets hope it gets fixed shortly
a calculated table cannot show in query editor,why? How do i show it in query editor.
thanks. my email:ydmwy@126.com
Calculated tables are generated after the data has been loaded into the model, so you can’t see or edit them in the Query Editor.
i have created a calculated column and a calculated table in data view on the power bi desktop. it cannot show my created calculated column and table in query editor.
why ? and how can i show the calculated column and table in query editor.
thanks.
Hi, Chris. I have created reports in Power BI, but I still have a lot to learn. With that I would like to seek help about creating relationship. I have a table with multiple columns. It has a lot of dates in it. I made a Date Table using CALENDARAUTO that I can use for a timeline slicer. My problem is that when I created relationship, I couldn’t connect multiple columns on a table to that Date Table, which result to wrong count in the visualizations. I noticed that the wrong counts of dates are on the columns that are not related to the date table. I believe that relating multiple columns to another column is not yet applicable in Power BI, but is there a workaround that I can do? Thank you so much in advance!
The workaround is to remodel your data so that you can create a relationship based on a single column. Usually the way you do this is by combining the values in each column into a single column.