DAX Other - SUMMARIZECOLUMNS function

Description

Returns a summary table over a set of groups.

DAX SUMMARIZECOLUMNS function is new in Excel 2016.

Syntax

SUMMARIZECOLUMNS (<groupBy_columnName>, [< groupBy_columnName >] …, 
   [<filterTable>] …, [<name>, <expression>] …)

Parameters

Sr.No.	Parameter & Description
1	groupBy_columnName A fully qualified column reference (Table[Column]) to a base table for which the distinct values are included in the returned table. Each groupBy_columnName column is cross-joined (different tables), or auto-existed (same table) with the subsequent specified columns.
2	filterTable A table expression which is added to the filter context of all columns specified as groupBy_columnName arguments. The values present in the filter table are used to filter before cross-join/auto-exist is performed.
3	name A string representing the column name to use for the subsequent expression specified.
4	expression Any DAX expression that returns a single value (not a table).

Return Value

A table which includes the combinations of values from the supplied columns, based on the grouping specified.

Only rows for which at least one of the supplied expressions return a non-blank value are included in the table returned.
If all expressions evaluate to BLANK/NULL for a row, that row is not included in the table returned.

Remarks

SUMMARIZECOLUMNS does not guarantee any sort order for the results.

A column cannot be specified more than once in the groupBy_columnName parameter.

Example

= SUMX ( 
   SUMMARIZECOLUMNS (Salesperson[Salesperson],  
   FILTER (Sales, Sales[Region]="South"), 
   "Sales Amount", SUMX (Sales, Sales[Sales Amount])), 
   [Sales Amount]
)

dax_functions_other.htm