How does the SUMMARIZECOLUMNS function (DAX) work?

The SUMMARIZECOLUMNS function (DAX) returns a summary table over a set of groups.


     <groupBy_columnName> [, <groubBy_columnName>] …, [<filterTable>] … [, <name>, <expression>] …

How do you use the SUMMARIZECOLUMNS function?

The SUMMARIZECOLUMNS function is very useful in Power BI report, especially to produce summarize/group/aggregate tables.

Related Blog Posts


Considerations when using the SUMMARIZECOLUMNS function

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 .

Related Video Tutorials

Formula examples using the SUMMARIZECOLUMNS function

SUMMARIZECOLUMNS ( ‘Sales Territory'[Category], FILTER(‘Customer’, ‘Customer’ [First Name] = “Alicia”) )

SUMMARIZECOLUMNS ( ‘Sales Territory'[Category], ‘Customer’ [Education], FILTER(‘Customer’, ‘Customer'[First Name] = “Alicia”) )

SUMMARIZECOLUMNS( Sales[CustomerId], “Total Qty”, IGNORE( SUM( Sales[Qty] ) ), “BlankIfTotalQtyIsNot3”, IF( SUM( Sales[Qty] )=3, 3 ) )

Related DAX Functions


Related Course Modules