SUMMARIZECOLUMNS Functon DAX

SUMMARIZECOLUMNS Function DAX

SUMMARIZECOLUMNS function is a Power Bi Table Manipulation function in DAX which returns a summary table over a set of groups.

DAX SYNTAX

SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]…,
[<filterTable>]…[, <name>, <expression>]…)

groupBy_columnName is a fully qualified column reference (Table[Column]) to a base table for which the distinct values are included in the returned table. Each groupBy_columnName column is cross-joined (different tables) or auto-existed (same table) with the subsequent specified columns.

filterTable is a table expression which is added to the filter context of all columns specified as groupBy_columnName arguments. The values present in the filter table are used to filter before cross-join/auto-exist is performed.

name is a string representing the column name to use for the subsequent expression specified.

expression is any DAX expression that returns a single value (not a table).

You can pass any number of parameters to SUMMARIZECOLUMNS function. If the first parameter is a reference to a column, the SUMMARIZECOLUMNS function understands that this is a column you would like to group by.

Lets look at an example of SUMMARIZECOLUMNS function in Power Bi.

We have a Dataset named as RegionSales, which contains the sales done by regions in particular years.

Region     Year        Sales

Lets create a summary table table using a SUMMARIZECOLUMNS function named as SummaryTable, which returns a new table that contains a  total sales by regions.

SummaryTable =
SUMMARIZECOLUMNS (
    RegionSales[Region],
    "OverALL Sum Of Sales By Region", SUM ( RegionSales[Sales] )
)

Now once you commit the DAX, it returns a total sales by regions. You can also see a table named as summarytable  is created in fields Pane.

As table Summarytable is already having a grouped rows based on region so, it takes a less time in comparision to actual RegionSales on display data on visual that helps you optimizing your report performance.

You can use now summarytable to dispaly regions by sales data on visuals as shown below.

SUMMARIZECOLUMN Function with FILTER

You can also use FILTER with SUMMARIZECOLUMN function, In reference of above example suppose you want to exclude North region from summarytable.

Lets modify above DAX by adding a Filter function as shown below.

SummaryTable =
SUMMARIZECOLUMNS (
RegionSales[Region],
FILTER (
ALL ( RegionSales[Region] ),
NOT ( RegionSales[Region] ) IN { "North" }
),
"OverALL Sum Of Sales By Region", SUM ( RegionSales[Sales] )
)

Lets display the data on visual, and you can see that summarytable does not have North Region data now.

Also Read..

Learn more on Power Bi DAX

Power Bi Page Navigation

Power Bi Dynamic Page Navigation 

Create Index column using a Power Query