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”.
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.
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.
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.
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.
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
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.
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.
But you can also pass multiple columns like this ALL(Products[SizeRange],Products[Color]).
Visualising these tables using New Table makes everything easier to understand.
