SUMMARIZECOLUMNS

SUMMARIZECOLUMNS is a DAX function that creates a summary table with requested totals over a set of groups.

Syntax

SUMMARIZECOLUMNS(
   [ GroupBy_ColumnName, ... ],
   [ FilterTable, ... ],
   [ Name, ... ],
   [ Expression, ... ]
)

Argument	Properties	Description
GroupBy_ColumnName	Optional, Repeatable	A column to group by or a call to ROLLUPGROUP function and ROLLUPADDISSUBTOTAL function to specify a list of columns to group by with subtotals.
FilterTable	Optional, Repeatable	An expression that defines the table from which rows are to be returned.
Name	Optional, Repeatable	A column name to be added.
Expression	Optional, Repeatable	The expression of the new column.

Return Values

A table which includes 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

• This function does not guarantee any sort order for the results.

• A column cannot be specified more than once in the groupBy_columnName parameter. For example, the following formula is invalid.

  SUMMARIZECOLUMNS( Sales[StoreId], Sales[StoreId] )

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Consider the following query:

SUMMARIZECOLUMNS ( 
    'Sales Territory'[Category], 
    FILTER('Customer', 'Customer' [First Name] = "Alicia") 
)

In this query, without a measure the groupBy columns do not contain any columns from the FILTER expression (for example, from Customer table). The filter is not applied to the groupBy columns. The Sales Territory and Customer tables may be indirectly related through the Reseller sales fact table. Since they’re not directly related, the filter expression is a no-op and the groupBy columns are not impacted.

However, with this query:

SUMMARIZECOLUMNS ( 
    'Sales Territory'[Category], 'Customer' [Education], 
    FILTER('Customer', 'Customer'[First Name] = "Alicia") 
)

The groupBy columns contain a column which is impacted by the filter and that filter is applied to the groupBy results.

The IGNORE syntax can be used to modify the behavior of the SUMMARIZECOLUMNS function by omitting specific expressions from the BLANK/NULL evaluation. Rows for which all expressions not using IGNORE return BLANK/NULL will be excluded independent of whether the expressions which do use IGNORE evaluate to BLANK/NULL or not. IGNORE can only be used within a SUMMARIZECOLUMNS expression.

Empty

Related functions

Other functions related to SUMMARIZECOLUMNS are:

• SUMMARIZE

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/summarizecolumns-function-dax