One of the latest addition to DAX has been the ability to apply an external filter context to SUMMARIZECOLUMNS, which makes this function a perfect replacement for SUMMARIZE in DAX. In The Definitive Guide to DAX I introduced such a function, but at the time of writing it was used only by Power BI and it wasn’t possible to include it in a measure, because of the limitation that now has been removed. Using SUMMARIZECOLUMNS you can write:
SUMMARIZECOLUMNS (
'Date'
[
Calendar
Year
],
'Product'
[Color],
"Sales Amount"
,
SUMX
(
Sales, Sales[Quantity] * Sales[Unit Price]
)
)
instead of:
SUMMARIZE (
Sales,
'Date'
[
Calendar
Year
],
'Product'
[Color],
"Sales Amount"
,
SUMX
(
Sales, Sales[Quantity] * Sales[Unit Price]
)
)
A more complete description of this new function is available in the Introducing SUMMARIZECOLUMNS article I wrote on SQLBI.
Create a summary table for the requested totals over set of groups.
SUMMARIZECOLUMNS ( [<GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] ] ] )
Creates a summary of the input table grouped by the specified columns.
SUMMARIZE ( <Table> [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] )
Returns the sum of an expression evaluated for each row in a table.
SUMX ( <Table>, <Expression> )