How does the SUMMARIZECOLUMNS function (DAX) work?
The SUMMARIZECOLUMNS function (DAX) returns a summary table over a set of groups.
SUMMARIZECOLUMNS Formula Syntax
<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 ) )