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:
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
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))
)
)
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.
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?
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.
Thanks for the clarification, Chris… 🙂
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().
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] )
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!
Hi Chris,
I don’t know if you can help me. I’m very new to DAX queries and don’t come from an IT background so I’m struggling with some issues at the moment. Your website, along with SQLBI, has become a key reference point for me.
I’m essentially trying to recreate departmental P&L’s using DAX (I’m doing this rather than through Power Pivot as it’s a table so users can add data such as forecast which I can then use as the basis for another query). Using my ‘monkey see, monkey do’ approach I’ve come quite far but am currently unable to insert subtotals after, for example, each P&L section. The DAX I’m using is below. Are you able to offer any suggestions?
EVALUATE
(SUMMARIZE
(TBL_TB_DATA,
TBL_DEPT_HIERARCHY[Dept Code FY16],
Tbl_Summ_Rpt_Sort[Summ Rpt Group Sort Order],
TBL_AX_COA_UPDATED[Summary Report Group],
Tbl_PL_Sort_Order[P&L Report SectionSort],
TBL_AX_COA_UPDATED[P&L Report Section],
TBL_AX_COA_UPDATED[Total Lvl 1 Account Description],Tbl_Lvl_1_Sort[Total Lvl 1 Account Description SortOrder],
TBL_AX_COA_UPDATED[Total Lvl 2 Account Description],Tbl_Lvl_2_Sort[Total Lvl 2 Account Description SortOrder],
“Current Year”,[Current Year],
“Budget”,[Budget FY16],
“Prior Year”,[Prior Year]
))
ORDER BY
[Summ Rpt Group Sort Order],
Tbl_PL_Sort_Order[P&L Report SectionSort],
Tbl_Lvl_1_Sort[Total Lvl 1 Account Description SortOrder],
Tbl_Lvl_2_Sort[Total Lvl 2 Account Description SortOrder]