SUMMARIZECOLUMNS DAX Function (Table manipulation)
Create a summary table for the requested totals over set of groups.
Syntax
| Parameter | Attributes | 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 for a row, that row is not included in the table returned.
Remarks
SUMMARIZECOLUMNS does not support evaluation within a context transition. This makes it not useful in most of the measures – a measure with SUMMARIZECOLUMNS can be called also by CALCULATE but not in any case of context transition, including other SUMMARIZECOLUMNS statements. Client tools like Excel and Power BI almost always generate context transitions to evaluate measures in the reports.
SUMMARIZECOLUMNS always combines all the filters on the same table into a single filter. The combined table resulting from this filter only contains columns explicitly listed in SUMMARIZECOLUMNS as grouping columns or filter columns. This is the auto-exists behavior that has side effects on functions such as FILTERS.
Filters in SUMMARIZECOLUMNS only apply to group-by columns from the same table and to measures. They do not apply to group-by columns from other tables directly, but indirectly through the implied non-empty filter from measures. In order to apply a filter to the group-by column unconditionally, apply the filter through a CALCULATETABLE function that evaluates SUMMARIZECOLUMNS.
Related articles
Learn more about SUMMARIZECOLUMNS in the following articles:
-
Introducing SUMMARIZECOLUMNS
This article explains how to use SUMMARIZECOLUMNS, which is a replacement of SUMMARIZE and does not require the use of ADDCOLUMNS to obtain good performance. » Read more
-
Understanding DAX Auto-Exist
This article describes the behavior of auto-exist in DAX, explaining the side effects of combining slicers on columns of the same table in Power BI. » Read more
-
Naming temporary columns in DAX
This article describes a naming convention for temporary columns in DAX expressions to avoid ambiguity with the measure reference notation. » Read more
Related functions
Other related functions are:
Last update: Jan 11, 2021 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
MSDN documentation: https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax