|Controlling the Position of Subtotals in DAX with GenerateAll()|
|Written by Chris Webb|
|Wednesday, 16 May 2012 02:20|
Reposted from Chris Webb's blog with the author's permission.
Today I’m teaching the SSAS 2012 Tabular workshop with Alberto Ferrari in Belgium, and an interesting question came up during my session on DAX queries to do with subtotals that I couldn’t answer immediately. However, I found a solution fairly quickly afterwards and so I thought it was worth blogging about – especially since I think it’s a better solution than the one that Alberto knew about already!
Consider this DAX query that runs on a table sourced from the DimDate table in Adventure Works:
It returns the number of Days in the table by calendar year and day number of week – it’s very similar to a basic GROUP BY query in SQL. I blogged about this use of Summarize() and Rollup() last year here.
The problem with this query is that all of the year subtotals (which appear as rows with blank values returned in the day number of week column) created by this query appear at the end of the result set, as you can see here:
This isn’t very clear though. How can we put each year’s subtotal at the end of the distinct list of day numbers instead? Alberto’s solution (and I think this is the solution we’ve got in our new SSAS 2012 Tabular book) involves using the IsSubtotal() function (see here for more details on this) and ordering, similar to this:
But I thought there was an alternative, more elegant approach and found one. Here it is:
As you can see, the subtotals appear after the list of day numbers for each year. There are two important things to understand about how this query works:
- Step by step guide on installing PowerPivot for SharePoint on a single machine
- List of suggested datasets to test PowerPivot
- How to install PowerPivot for Excel and list of know issues
- List of PowerPivot DAX functions with description
- Microsoft Virtual Lab: PowerPivot for Excel 2010 Introduction
- Learning PowerPivot and DAX
- List of PowerPivot DAX functions (short)
- PowerPivot's impact on BI pros?