summarize vs summarizecolumns | how to use summarize and summarizecolumns in DAX with examples

In todays dax tutorial we see the difference between summarize and summarizecolumns dax function in Power BI and also how to use them.
Syntax of SUMMARIZE Dax : SUMMARIZE (table, groupBy_columnName[, groupBy_columnName]…[, name, expression]…)

Parameters Used:

Term Definition
table Any DAX expression that returns a table
groupBy_ColumnName (Optional) The name of an existing column used to create summary groups based on the values found in it. This parameter cannot be an expression.
name The name given to a total /summarize column
expression Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).
SUMMARIZE Dax Parameter Explained
Summarize can be used in the following ways :

1.Return distinct list of values as shown in the below code:

Distinct Countries =
SUMMARIZE(financials,
financials[Country]
)
2.Return a summary table containing aggregated values as shown below:

Sales/Profit per Country per Yr =
SUMMARIZE(financials,
financials[Country],
financials[Year],
"Sales",sum(financials[ Sales]),
"Profit",sum(financials[Profit]))
3.We can use summarize inside a measure as shown below.

Measure-MAX_Sales/Profit per Country per Yr =

Var a1=
SUMMARIZE(
financials,
financials[Country],
financials[Year],
"Sales3",MAX(financials[ Sales])
)


var b=sumx(a1,[Sales3])
return b
Now let us see what is the difference between Summarize and SummarizeColumns?

As you had seen above Summarize merely summarizes a table without filtering .SummerizeColumn can add filtering capability to Summarize as shown below in the syntax:

Syntax of SummarizeColumns :

SUMMARIZECOLUMNS( groupBy_columnName [, groupBy_columnName ]…, [filterTable]…[, name, expression]…)
Parameters
Term Definition
groupBy_columnName A column reference (Table[Column]) to a base table for which the distinct values are included in the returned table.
filterTable A table expression which is added to the filter context of all columns specified as groupBy_columnName arguments.
name The name for the subsequent expression specified.
expression Any DAX expression that returns a single value
Parameters of SUMMARIZECOLUMNS
Return value
A table which includes combinations of values from the supplied columns based on the grouping .

Remarks :

When creating a table try using SummarizeColumns instead of Summarize .Remember SummarizeColumns is quicker .

When creating a measure try using Summarize since Summarize column will not work in Context transition (in Tables, Matrix ,Charts) .It only works in cards.
For more details check out :

#powerbi #powerbitraining #powerbidax #powerbi_training #daxfunctions #powerbizone

Chapters:
0:15 :Introduction to difference between Summarize and Summarizecolumn DAX in Power BI
1:18 Syntax of Summarize Dax in Power BI
2:32 Example 1 of Summarize Dax-creating tables
2:40 Example 2 of Summarize Dax -creating tables
2:38 Example 1 of Summarizecolumns Dax-creating tables
4:49 Example of Summarize Dax (Using Measure)
5:18 Example of Summarizecolumns Dax (Using Measure)
6:03 Error explained while using Summarize column in measures

Check out other Power BI Tutorials Videos :

Learn Power BI through our vlog and Free Videos:

You can download the pbix file for your self practice sessions from :
Do not forget to Like ,Subscribe and comment which keeps me motivated !

As promised do visit the below useful link for more details on this topic .

[powerbizone]

You can download the pbix file for your self practice sessions from :
Do not forget to Like, Subscribe and comment which keeps me motivated !

[er]

Spot on video. Surprisingly, there aren't that many vids on SummarizeColumns and none show use of the filter. Thank you

[TheChandansss]

We can use filter in summarizecolumn as well. Difference is between table load. Summarizecoloumn is more optimised

[jayantmishra]

Hi...
Can you help me to solve this question ??

Which of the following DAX function will return a table :-

1 SUMMARIZECOLUMNS
2 CALCULATETABL
3 RELATEDTABLE
4 All of the above
5 None of the above

Please answer the question??

[SusanHolder-lipm]

SummarizeColumns worked in my data model perfectly. Then I imported my measure into my report and using the same exact visual, I got the error from your video. It's curious that it worked in one report but not another.