DAX equivalent of GROUP BY CUBE ()

[bseefelt]

I'm using SUMMARIZE(...ROLLUP...) in DAX which accomplishes the same as a GROUP BY ROLLUP() in T-SQL and so far so good. Is there a DAX statement that will do the same as GROUP BY CUBE()? I want to get all the other permutations of rollup as well. Thanks.

[v-ljerr-msft · Accepted solution]

@bseefelt

You should be able to use SUMMARIZECOLUMNS(...ROLLUPADDISSUBTOTAL..) in DAX to do the same as GROUP BY CUBE() in T-SQL. See my sample below.

Assume we have a table called "Inventory" like below.

Then we should be able to use the formula below to create a summary table the same as GROUP BY CUBE().

Table 2 = 
SUMMARIZECOLUMNS (
    ROLLUPADDISSUBTOTAL ( Inventory[Item], "IsItemSubTotal" ),
    ROLLUPADDISSUBTOTAL ( Inventory[Color], "IsColorSubTotal" ),
    "TotalQuantity", SUM ( Inventory[Quantity] )
)

Regards

[bseefelt]

That worked perfectly.  Thank you so much!