Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Controlling the Position of Subtotals in DAX with GenerateAll()

with 6 comments

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:

evaluate
summarize(
DimDate
, DimDate[CalendarYear]
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, countrows(DimDate)
)

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:

image

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:

evaluate
summarize(
DimDate
, DimDate[CalendarYear]
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, countrows(DimDate)
, "Is Subtotal"
, IsSubtotal(DimDate[DayNumberOfWeek])
)
order by
DimDate[CalendarYear] ASC
, DimDate[DayNumberOfWeek] ASC
, [Is Subtotal] ASC

image

 

But I thought there was an alternative, more elegant approach and found one. Here it is:

evaluate
generateall(
values(DimDate[CalendarYear])
,
summarize(
DimDate
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, calculate(countrows(DimDate))
)
)

image

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:

  • I’m using the GenerateAll() function to take the list of distinct years returned by the Values() function and then, for each year, return a table of day numbers with a subtotal and crossjoin the result. This gives us our subtotals in the correct position without any sorting required.
  • I had to wrap my original countrows() with calculate to make sure it returned the correct value.

Written by Chris Webb

May 15, 2012 at 11:51 am

Posted in DAX

6 Responses

Subscribe to comments with RSS.

  1. Hi Chris,
    Great post! By the way, how would the performance of the second method compare with the first method? Wouldn’t a generate and summarize be slower than a summarize and order by?

    Jason Thomas

    May 15, 2012 at 12:31 pm

    • Generate probably would be slower, but for any report like this I think the data volumes involved would be so small you wouldn’t notice the difference.

      Chris Webb

      May 15, 2012 at 1:05 pm

      • Thanks for the clarification, Chris… :)

        jason

        May 15, 2012 at 1:46 pm

  2. Alberto has also pointed out that in my GenerateAll() example I still need an Order By clause to ensure that the values are returned in the correct order – DAX is like SQL in that respect. However that’s beside the point – the major difference between the two approaches is the use of IsSubtotal() and GenerateAll().

    Chris Webb

    May 15, 2012 at 1:58 pm

  3. In Alberto’s solution, in order to get the subtotal below day #7 instead of before day #1 you would need to modify the ORDER BY clause so that [Is Subtotal] precedes DimDate[DayNumberOfWeek].
    (I.e. ORDER BY DimDate[CalendarYear], [Is Subtotal] , DimDate[DayNumberOfWeek] )

    ruve1k

    May 15, 2012 at 8:30 pm

  4. As Jason pointed out, the only concern is about performance. WIth a large table, impact could be quite large. It would be nice sharing some metrics if someone has time to compare performance of the two approaches!

    Marco Russo

    May 16, 2012 at 1:43 pm


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

Follow

Get every new post delivered to your Inbox.

Join 3,072 other followers

%d bloggers like this: