How to use SUMMARIZECOLUMNS Function (DAX) in Power BI and SSAS Tabular

SUMMARIZECOLUMNS function returns a summary table over a set of groups. This new function is included in SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop. SUMMARIZECOLUMNS, which is a replacement of SUMMARIZE and does not require the use of ADDCOLUMNS to obtain good performance

Power BI

SUMMARIZECOLUMNS function is very useful in Power BI report, especially to produce summarize/group/aggregate tables.

For example, if you need to create a summary table for sales value by subcategory, a year with some filters.

Summary Product Subcategory = SUMMARIZECOLUMNS

(

'Product Category'[Category],

'Product Subcategory'[Subcategory],

'Date'[Calendar Year],

FILTER('Product Category','Product Category'[Category]="Audio"),

"Total Sales",'Sales'[Sales Amount]

)

SSAS Tabular

You can use the function in SSAS cubes in similar ways, but you need to be aware how to use the syntaxes.

The following examples will give a clear idea about how to use SUMMARIZECOLUMNS function.

EVALUATE

SUMMARIZECOLUMNS

(

'Date'[Calendar Year],

"Total Sales",'Internet Sales'[Internet Total Sales]

)

EVALUATE

SUMMARIZECOLUMNS

(

'Product Category'[Product Category Name],

'Date'[Calendar Year],

"Total Sales",'Internet Sales'[Internet Total Sales]

)

EVALUATE

SUMMARIZECOLUMNS

(

'Product Category'[Product Category Name],

'Date'[Calendar Year],

FILTER('Date','Date'[Calendar Year]=2014),

"Total Sales",'Internet Sales'[Internet Total Sales],

"Total Units",'Internet Sales'[Internet Total Units]

)

EVALUATE

SUMMARIZECOLUMNS

(

'Product Category'[Product Category Name],

'Date'[Calendar Year],

FILTER('Date','Date'[Calendar Year]=2014),

"Total Sales",'Internet Sales'[Internet Total Sales],

"Total Sales with Correction",'Internet Sales'[Internet Total Sales]*0.99,

"Total Units",'Internet Sales'[Internet Total Units]

)

EVALUATE

SUMMARIZECOLUMNS

(

'Product Subcategory'[Product Subcategory Name],

'Date'[Calendar Year],

FILTER('Product Category','Product Category'[Product Category Name]="Bikes"),

"Total Sales",'Internet Sales'[Internet Total Sales],

"Total Units",'Internet Sales'[Internet Total Units]

)

Cheers! Uma