DAX Other - SUMMARIZECOLUMNS function


Advertisements

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

Useful Video Courses


Video

Mastering DAX and Data Models in Power BI Desktop

53 Lectures 5.5 hours

Abhay Gadiya

Video

Mastering DAX Studio

Featured

24 Lectures 2 hours

Randy Minder

Video

DAX / Power BI - Customer and Sales Analysis Deep Dive

26 Lectures 4.5 hours

Randy Minder

Advertisements