SUMMARIZECOLUMNS DAX Function (Table manipulation)

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

Syntax

SUMMARIZECOLUMNS ( [<GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] ] ] )
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.
The expression can be embedded in a NONVISUAL function, which marks a value filter in SUMMARIZECOLUMNS function as not affecting measure values, but only applying to group-by columns.

Name Optional
Repeatable

A column name to be added.

Expression Optional
Repeatable

The expression of the new column.

Return values

Table An entire table or a table with one or more columns.

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

Until February 2023, SUMMARIZECOLUMNS did not support evaluation within a context transition at all. In products released before that month, this limitation made SUMMARIZECOLUMNS not useful in most of the measures – it was not possible to call a measure SUMMARIZECOLUMNS 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. From February 2023, the context transition is supported in a few scenario, but not in all the conditions.

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.

The values obtained for the group-by columns include the blank values of invalid relationships.
For example, the following SUMMARIZECOLUMNS function:

SUMMARIZECOLUMNS (
    'Date'[Calendar Year],
    'Product'[Color],
    "Result", [Sales Amount]
)

is like an optimized version of the following DAX expression:

FILTER (
    ADDCOLUMNS (
        CROSSJOIN (
            VALUES ( 'Date'[Calendar Year] ),
            VALUES ( 'Product'[Color] )
        ),
        "Result", [Sales Amount]
    ),
    NOT ( ISBLANK ( [Result] ) )
)

SUMMARIZECOLUMNS does not preserve the data lineage of the columns used in ROLLUPADDISSUBTOTAL or ROLLUPGROUP. If such columns are later used in the filter context, they are ignored. This behavior is different from SUMMARIZE, where the same situation raises an error.

» 8 related articles
» 7 related functions

Examples

--  SUMMARIZECOLUMNS is the primary querying function in DAX
--  It provides most querying features in a single function:
--      First set of arguments are the groupby columns
--      Second set are the filters
--      Third set are additional columns added to the resultset
EVALUATE
SUMMARIZECOLUMNS ( 
    'Product'[Brand],
    'Date'[Calendar Year],
    TREATAS ( { "CY 2008", "CY 2009" }, 'Date'[Calendar Year] ),
    TREATAS ( { "Red", "Blue" }, 'Product'[Color] ),
    "Amount", [Sales Amount],
    "Qty", SUM ( Sales[Quantity] )
)
Brand Calendar Year Amount Qty
Contoso CY 2008 449,704.15 1,851
Wide World Importers CY 2008 87,108.20 213
Northwind Traders CY 2008 152,389.53 263
Adventure Works CY 2008 74,735.98 230
Southridge Video CY 2008 9,902.09 688
Litware CY 2008 244,134.07 611
Fabrikam CY 2008 274,937.21 557
Proseware CY 2008 50,642.52 178
A. Datum CY 2008 27,733.20 60
Tailspin Toys CY 2008 22,856.00 677
Contoso CY 2009 298,096.75 1,703
Wide World Importers CY 2009 75,496.96 312
Northwind Traders CY 2009 73,747.19 156
Adventure Works CY 2009 81,845.29 216
Southridge Video CY 2009 10,696.16 692
Litware CY 2009 184,703.58 756
Fabrikam CY 2009 222,621.82 506
Proseware CY 2009 71,255.37 371
Tailspin Toys CY 2009 51,084.87 1,797
--  SUMMARIZECOLUMNS can compute subtotals as part of the query result
EVALUATE
SUMMARIZECOLUMNS (
    ROLLUPADDISSUBTOTAL ( 'Product'[Brand], "Brand total" ),
    ROLLUPADDISSUBTOTAL ( 'Date'[Calendar Year], "Year total" ),
    TREATAS ( { "CY 2008" }, 'Date'[Calendar Year] ),
    TREATAS ( { "Contoso", "Litware" }, 'Product'[Brand] ),
    "Amount", [Sales Amount],
    "Qty", SUM ( Sales[Quantity] )
)
ORDER BY [Year Total] ASC, [Calendar Year], [Brand Total] ASC, [Brand]
Brand Calendar Year Brand total Year total Amount Qty
Contoso CY 2008 false false 2,369,167.68 14,901
Litware CY 2008 false false 1,487,846.74 3,364
(Blank) CY 2008 true false 3,857,014.43 18,265
Contoso (Blank) false true 2,369,167.68 14,901
Litware (Blank) false true 1,487,846.74 3,364
(Blank) (Blank) true true 3,857,014.43 18,265
--  Blank values are automatically removed from the output, 
--  unless you use the IGNORE modifier for newly introduced
--  columns.
--  Removed rows can also be reintroduced later by using ADDMISSINGITEMS
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year], 
    "Amount", [Sales Amount]
)

EVALUATE
ADDMISSINGITEMS ( 
    'Date'[Calendar Year], 
    SUMMARIZECOLUMNS (
        'Date'[Calendar Year], 
        "Amount", [Sales Amount]
    ),
    'Date'[Calendar Year]
)
ORDER BY 'Date'[Calendar Year]

Calendar Year Amount
CY 2007 11,309,946.12
CY 2008 9,927,582.99
CY 2009 9,353,814.87
Calendar Year Amount
CY 2005 (Blank)
CY 2006 (Blank)
CY 2007 11,309,946.12
CY 2008 9,927,582.99
CY 2009 9,353,814.87
CY 2010 (Blank)
CY 2011 (Blank)
-- This query returns 0 rows, because all the Sales transactions
-- have an Order Date that exists in Date
EVALUATE
CALCULATETABLE (
    VALUES ( Sales[Order Date] ),
    TREATAS ( { BLANK () }, 'Date'[Date] )
)
ORDER BY Sales[Order Date]

-- This query returns a list of all the unique values in Sales[Order Date]
-- because the filter applied to 'Date'[Date] has only effects on the Date table
-- The Sales table is unaffected by that filter.
EVALUATE
SUMMARIZECOLUMNS (
    Sales[Order Date],
    TREATAS ( { BLANK () }, 'Date'[Date] )
)
ORDER BY Sales[Order Date]

-- This query returns 0 rows, because COUNTROWS ( Sales ) 
-- always returns blank, being executed in a filter context 
-- that includes 'Date'[Date] and Sales[Order Date]
-- Because there are no rows in Sales with an invalid Order Date,
-- there are no rows with a non-blank result for COUNTROWS ( Sales )
EVALUATE
SUMMARIZECOLUMNS (
    Sales[Order Date],
    TREATAS ( { BLANK () }, 'Date'[Date] ),
    "# Transactions", COUNTROWS ( Sales )
)
ORDER BY Sales[Order Date]

SUMMARIZECOLUMNS cannot be used if the filter context contains a row context or a filter context generated by context transition or an arbitrarily shaped set. This limitation makes it unusable in any measure. It is a function intended solely for queries and calculated tables.
The following query produces an error:

EVALUATE
ADDCOLUMNS (
    VALUES ( 'Date'[Calendar Year] ),
    "Test",
        SUMX (
            SUMMARIZECOLUMNS (
                'Product'[Brand],
                "Qty", SUM ( Sales[Quantity] )
            ),
            [Qty]
        )
)

Related articles

Learn more about SUMMARIZECOLUMNS in the following articles:

Related functions

Other related functions are:

Last update: Mar 27, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo, Bill Neil, Colin Maitland , Kenneth Barber

Microsoft documentation: https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of SUMMARIZECOLUMNS? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.