Using ROLLUP with the SUMMARIZE function

In this recipe, you will use the ROLLUP function within an argument for SUMMARIZE to show all totals and subtotals in the query. This will be similar to using group by with rollup in T-SQL. You will determine the number of crashes based on weather and manner of crash to get the grouping and the totals by group. The SUMMARIZE function has a required syntax of SUMMARIZE(<table>,<groupby_columnname>).

How to do it...

1. Connect to the CHAPTER_9_DAX database in SQL Server Management Studio.

2. Right-click on the database, and select New Query | MDX to create a new MDX query window.

3. To determine the number of fatalities by the manner of the crash combined with the weather condition, you will use the SUMMARIZE function along with the EVALUATE function to return a table. To return all records that are related to these conditions, you need to use the ROLLUP function:

           EVALUATE 
           SUMMARIZE ( 
               CRASH_DATA_T, 
         ...