DAX summarize function in parallel with SQL

DAX summarize function is used to group the data. If you are coming from the SQL world, then summarize function in DAX behaves like group by function in SQL.

The syntax of summarize function in DAX is

SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)

<table>: The first parameter of summarize function is a table. You can use here any dax function that returns a table.

<groupBy_columnName>: In the second parameter, you need to provide a column on which grouping is required. For example, if we want to see the product sales by the product column, then the product column will be used in the second parameter. We can use here multiple columns for grouping.

Name: After <groupBy_columnName> parameter(s), in the next parameter, the column name of your choice is required, which will hold the product sale.

<expression>: In the last parameter, we need to provide an expression on which we want grouping.

Group by function in SQL

Let’s try to calculate the total number of available subcategories for every category in SQL. To achieve this, we need to join the dimProductCategory table with the dimProductSubcategory table, as you can see below.

Select
pc.EnglishProductCategoryName,
[Total categeories] = count(psc.ProductSubcategoryKey)
from dimProductCategory pc
INNER JOIN dimProductSubCategory psc ON pc.ProductCategoryKey = psc.ProductCategoryKey
Group by
pc.EnglishProductCategoryName

If we execute this query, it will produce the following result.

DAX summarize function example

Now we need to achieve the same result in DAX as we got in SQL. We can get our objective by writing the following DAX expression in DAX Studio.

EVALUATE
SUMMARIZE(dimProductCategory,
            dimProductCategory[EnglishProductCategoryName],
            "Total subcategories",
            COUNTROWS(dimProductSubCategory)
        )

You can see in the DAX expression we didn’t need to write explicit inner joins. DAX expressions are much cleaner than the SQL.

The first parameter of dax summarize function consist of dimProductCategory, which is the name of the table. In the second parameter, we have used the dimProductCategory[EnglishProductCategoryName] column on which we need grouping, as you can see below.

In the third column, we have written the required column’s name, holding the value of the fourth column that is countrows(dimProductSubcategoryKey). The fourth parameter contains countrows function to count the subcategories. In this way, we are getting total categories for each category.
You have seen how summarize function in dax is similar to group by in SQL.

Distinct function in SQL

If we need to get unique rows from the provided column, then there are two SQL ways.

• by using a distinct function
• by using group by function

Let’s try to get colors from the product table. If we write the following query, we will get 397 rows because of duplicated values in the color column.

Select
Color
From
Products

If we use the distinct function, we will our required result, as you can see below.

Select distinct
Color
From
Products

The result of the above query will be

Let’s try to achieve the same unique color without using a distinct function in SQL by writing the following query.

Select
Color
From Products
Group by Color

Group by function in SQL remove all the duplicate rows and produce a unique set of rows
Now we have to use summarize function in dax to achieve unique colors.

Distinct values using summarize function in dax

In the previous example, we have seen how we can use an aggregated function within the Summarize function to achieve the total number of subcategories for each category.

We can use DAX summarize function to get unique values for the provided column. 

Let’s write one dax expression to get distinct colors.

EVALUATE
SUMMARIZE(Products,Products[Color])

The output of the above dax expression is

We got unique results perfectly. If you look closely at the dax expression, we just provided two parameters. The first parameter is the table name, and the second parameter is the name of the column for which we need unique values.
We have talked enough about dax summarize function and its uses. It is convenient and can be used to solve a lot of business issues where grouping is required between different tables and columns.

For more useful blogs, please visit Learn DAX