For many years, SUMMARIZECOLUMNS has been a function dedicated to DAX queries and calculated tables, but it was not supported in DAX measures. Over time, Microsoft lifted the limitations, and in June 2024, the function was declared as fully supported in measures. However, we never suggested widely adopting it because we wanted to study its behavior in detail, which was not necessary for the queries due to the limited side effects in that context. Time well spent, as we have now been able to document in detail how SUMMARIZECOLUMNS works, what to do, and what not to do.

You will find a short version of the best practices in an upcoming article (weeks). However, if you want to delve deeper, the Understanding SUMMARIZECOLUMNS whitepaper is for you. We also have extensive coverage of concepts such as auto-exists (also known as clustering), non-empty, and value filter.

This paper is not an introduction to SUMMARIZECOLUMNS. We assume our readers are familiar with the syntax of SUMMARIZECOLUMNS, evaluation contexts, context transition, and all the fundamental concepts of DAX.

The content has the following modules:

  • Goals and prerequisites
  • Why is it so crucial to understand SUMMARIZECOLUMNS?
  • Introducing SUMMARIZECOLUMNS results
  • Introducing group-by tuples
  • Understanding non-empty
  • Understanding NONVISUAL and how filters are applied
  • Understanding CROSSJOIN versus SUMMARIZE
  • Understanding clustering and auto-exists
  • Understanding filters spanning multiple tables
  • Understanding value filter behavior
  • Putting things together
  • Understanding ALLSELECTED in SUMMARIZECOLUMNS
  • SUMMARIZECOLUMNS and external filters
    • Internal filters overriding outer filters
    • Group-by columns keeping outer filters on the same table only
  • SUMMARIZECOLUMNS and data lineage
  • SUMMARIZECOLUMNS best practices
    • Basic grouping, with no measures
    • Basic grouping, with measures
    • Complex grouping
    • Grouping and filtering
  • Conclusions

Once again, a big thank you to all SQLBI+ subscribers for their support, which enables us to continue producing high-quality content on advanced topics like this one.

SUMMARIZECOLUMNS

Create a summary table for the requested totals over set of groups.

SUMMARIZECOLUMNS ( [<GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] ] ] )

NONVISUAL

Mark the filter as NonVisual.

NONVISUAL ( <Expression> )

CROSSJOIN

Returns a table that is a crossjoin of the specified tables.

CROSSJOIN ( <Table> [, <Table> [, … ] ] )

SUMMARIZE

Creates a summary of the input table grouped by the specified columns.

SUMMARIZE ( <Table> [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] )

ALLSELECTED
CALCULATE modifier

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied inside the query, but keeping filters that come from outside.

ALLSELECTED ( [<TableNameOrColumnName>] [, <ColumnName> [, <ColumnName> [, … ] ] ] )