Calculated Tables In Power BI

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:

image

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:

MyFilteredDimDateTable =
FILTER (
    DimDate,
    DimDate[CalendarYear] = 2002
        && DimDate[EnglishMonthName] = "June"
        && DimDate[DayNumberOfMonth] < 5
)

image

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

CalendarDemo =
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:

image

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?

17 thoughts on “Calculated Tables In Power BI

  1. 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.

  2. 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.

  3. 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

  4. […] Then under the data modelling section I have to say that I am currently not any DAX guru, but I do appreciate how powerful it is, and how you can really extend your data with so many DAX functions. In particular is the Calculated Table, which Chris Webb has already blogged about and has some great information here: Calculated Tables In Power BI […]

  5. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s