I’ve been meaning to blog about this for a while – today’s the day :-).  Inside Power BI Desktop is a relatively new feature called “New Table”.

image

This is only available in Power BI Desktop and not in any of the Excel versions or SSAS Tabular.  This feature is essentially a “Calculated Table” function.  You can pass any valid DAX measure that returns a table of values, and the table will be materialised and loaded into the data model.

Why Would you Want to Do This?

I generally recommend pushing data shaping as close to the source as is practicable.  If your source (eg SQL Server) doesn’t contain the data in the shape you always need, then it is in your interest to get it changed back at the source if you can – that way it will always be available in the shape you actually need it.  But that is not always possible of course.  If that were not possible, it would normally be my preference to reshape the table during data load with Power Query rather than creating a table in Power BI Desktop with New Table. Having said that, there are some use cases I can see where New Table can be helpful.

You Need a Summary Table with Complex Calculations

One advantage of using New Table vs Power Query is you have access to all your measures in the data model.  As an example, assume you want to produce a visualisation like this one.

image

In this case, the above table (Adventure Works) is not overly complex and could be created quickly at run time in a Table Visual in Power BI Desktop.  But in real life it is possible that the measures may be very complex and you may have 10s of millions of rows of data.  In this scenario you may want to pre-calculate a summary table like this and have it loaded as a table of values during data refresh.  Using New Table in this way pushes the calculation effort so it is executed when the data is loaded, not at run time.  In addition because the table is created in the data model (not Power Query) you are able to use the measures written in the data model rather than have to recreate the logic elsewhere.

A Little DAX Query Language Helps

DAX as a query language is not hard to pick up, and you can use New Table as a tool to help you learn.  To create the table above, I simply clicked “New Table” and entered a summarize statement as you can see below.  Note that DAX as a query language normally requires you to start every query with the EVALUATE statement, but this is not needed in a Calculated Table.

image

In the example above I have started with a SUMMARIZE statement.  The most important thing to know about SUMMARIZE is you must start with a data* table (1 above) and then add one or more columns that you want to see in the new summarized table (2 and 3 above).  Each column after the table name must be in either the data table itself or it must be accessible from a many to 1 relationship from the data table (it can pass through many tables as long as the relationships are all many to 1 from the data table).

*NOTE:  You can write a SUMMARIZE starting from a lookup table and add columns from the lookup table, but you can’t then also add columns from a data table.

As you can see above, this simple query creates a list of all sub categories and financial years only if there were transactions for that tuple in the sales table (35 rows in total). In this sense it is different to CROSSJOIN as CROSSJOIN would produce all technically possible combinations of 2 tables.  In the formula below I use CROSSJOIN to create a summary table of all the possible values of Product[SubCategory]) and Calendar[Fin Year] by first converting these columns to tables (with values) and then joining them.  There are 148 possible combinations (rows) below.

image

Now I have the Sub Category and Year in my original SUMMARIZE table, I want to add the sales, cost and margin for each tuple.  This can be done using SUMMARIZE, but best practice is that you should use ADDCOLUMNS instead.  ADDCOLUMNS takes a table (in this case the SUMMARIZE table I created above) and then you can add one or more additional columns to the table as required.    So I already had line 3 below, so I simply wrapped it inside ADDCOLUMNS and then added the 3 columns you can see below.  Note that ADDCOLUMNS operates in a row context and doesn’t have a filter context, hence it relies on the implicit CALCULATE wrapped inside each measure to get the right outcome.

image

In the simplistic example I am showing you here the measures [Total Sales], [Total Cost] and [Total Margin] are not at all complex, however you can probably imagine that this would be a good approach if you had very complex measures in very large data models that required a summary table for some reason.

Test Your Measures

The other reason I like New Table is to use it to debug a stubborn measure.  There are lots of measures that take a table function as in input.  Table functions common inside measures include ALL(), VALUES() and FILTER() to name a few.  It has always been difficult (especially for Excel pros) to “visualise” what each of these table functions is doing because they essentially create a virtual table that is never materialised.  You only ever get to see the final scalar value result from the measure itself.  And if the scalar value result is not working as expected, it can be hard to work out if it is the table or something else that is causing the problem.  Enter New Table as a debugging tool.

I am going to refer back to this blog post I wrote 2.5 years ago (OMG, has it been that long!).  In that article I explained how I didn’t really understand the nuances of filter propagation and context transition in my learning stages of DAX.  Here is a quote from that blog post.

This FILTER( ) function will return a table. I can’t actually see the table, hence for debugging purposes I am going to wrap the entire Function in another COUNTROWS( ) function just for this test.
Test:=COUNTROWS(
FILTER(
Products,
COUNTROWS(Forecast)>0
)
)

The point I am making is that I used the COUNTROWS function to help me “visualise” what FILTER was returning, because I couldn’t actually see the table.  But in Power BI Desktop that all changes.  All I need to do now is copy the FILTER portion of any formula and create a New Table as shown here

image

Now I get to see that all the rows are visible and also I can physically see the data – this gives me a warm fuzzy feeling of comfort that I really understand what is going on under the hood.  The point of the post I am referring to (from 2.5 years ago) is that I was missing a CALCULATE in my FILTER Function.  If I now change the Calculated Table in Power BI Desktop to include the correct formula with a CALCULATE(), I get the following result.

image

Now I can see that the FILTER function is returning a correctly filtered table (as I needed for the example), and “seeing” it materialised by using a Calculated Table has really helped me get my head around what is going on.

Some Quick ALL( ) Examples

ALL is a function that I normally use inside CALCULATE to remove filters.  But it can return some other interesting tables using columns in the syntax, and these are easy to visualise with New Table.

When you wrap ALL around a column, you get a distinct list just like you would with the VALUES function.

image

But you can also pass multiple columns like this ALL(Products[SizeRange],Products[Color]).

image

Visualising these tables using New Table makes everything easier to understand.

  Subscribe to PowerPivotPro!
X

Subscribe

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. 

This Post Has 13 Comments

  1. Wow, Matt! This is a fantastic post: Summarize and Crossjoin in the same post, AND… using the tool to help visualize what’s going on inside Filter and All! This opens up some great applications for those learning DAX in Power BI Desktop.

    1. Thanks for your feedback Adam. I have to admit with all the problems with the Excel UI plus the rise and rise of Power BI, I am also thinking of doing more in Power BI Desktop. It is certainly a more enjoyable experience writing DAX in Power BI Desktop 🙂

      1. Hi Matt,

        last month I was called words by a client of mine because of the Excel problem you call out. I really hope that Microsoft will do something about it especially after the effort you and your fellow MVP’s have made to have it corrected/fixed. Anything new on that front?

  2. Why is using addcolumns a best practice? As it happens my constructed Dates table was done using ADDCOLUMNS on a CALENDAR 2004-2025 with limiting columns for first and last effective dates in the range based on the fact tables loaded (i.e. “Finance-Curr”,IF(AND([Date]>=DATE(YEAR(MIN(Finance[TRANSDATE])),01,01),[Date]<=DATE(YEAR(MAX(Finance[TRANSDATE])),12,31)),TRUE(),FALSE()) ). But I'd like to understand the rationale for choosing ADDCOLUMNS over other options.

    1. I learnt all of my query dax from Sqlbi.com and the articles linked by Matthew explain why (plus the article is already linked in my post). It has to do with the way the engine operates under the hood. summarize is efficient in doing the “group by” portion but not efficient with the extra measure columns. There is a new function out that fixes this problem that you can read about here, but it is only available in the latest release. http://www.sqlbi.com/articles/introducing-summarizecolumns/

    1. Hi Miguel,

      I’m a bit curious regarding the VALUES(SELECTCOLUMNS()) construction. My understanding is that VALUES() can take a tablereference or a columnreference as a parameter but not a tableexpression (SELECTCOLUMNS() would constitute a tableexpression in this case). Could you please show us an example to illustrate what you mean?

  3. Thanks Matt !!
    Materialising Tables/Filters is probably one of the trickiest thing to learn in DAX, especially for the people (like me) who have an Excel background.
    DAX Studio is also a very good tool to visualise these tables – especially for those who do not (or cannot :/) use Power BI Desktop for the moment !

    I wonder if the language running behind DAX is something close to SQL ? Because the logic of DAX functions, especially DAX query functions, is very similar to SQL!

  4. Nice post Matt – always a joy to read. This feature is also available in SSAS Tabular 2016 (Table > New Calculated Table or by hitting the +fx widget to the right of your tabs). For certain types of tables, using the approach will outperform pretty much any other ETL process so it’s incredibly useful.

Leave a Comment or Question