Aggregated Table in Power BI – Using GroupBy Function in DAX

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

There are many different ways you can create aggregations in Power BI, You can do it in the source (using the database t-SQL language), or using Group By operation in Power Query. You can also do it in DAX using some functions. One of the functions that can be used for grouping and aggregation is Group By. This article is about how to use Group By in DAX. Creating aggregation using DAX is a very useful skill because you can use it to create virtual tables in your measures and have better dynamic calculations in Power BI.

Sample Data

My sample dataset table is DimCustomer as below;

GroupBy Function

GroupBy DAX function can be used as below:

GROUPBY( <table>, <grouping column1>, [<output aggregation column name>, <expression for aggregation column>]…)

  • The Table can be a table in your dataset, or any virtual table (means any function that returns a table).
  • grouping column1: the column that you want the resultset to be grouped by that column. this is an existing column in the table above.
  • name: the name that you give to the new column created.
  • expression: the expression that is used for the new column created.
  • you can have more than one grouping column.

Just Grouping

As an example, a basic usage of GroupBy is to create a list of unique EnglishEducation values from the DimCustomer table with the below expression:

This is used to create a calculated table, and here you can see the result:

In the above example, the table is DimCustomer, and the Grouping happens on the EnglishEducation column of that table. The result is the grouped list of EnglishEducation. Which is the same result compared to using DISTINCT or VALUES functions. If you just want a distinct list, you might use one of the other two functions rather than GroupBy. Using GroupBy function is normally comes with a new column which is the aggregated result.

Grouping and Aggregation: CurrentGroup

If you want to do grouping and aggregation using the GroupBy, there is an important function you should use, which is CurrentGroup. Let’s say you want to calculate count of customers within each group of EnglishEducation. You might think of an expression like this:

The Row Count column supposed to show the count of customers for each EnglishEduction group. However, if you use it like the above expression, you will get this error:

Function ‘GROUPBY’ scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().

CurrentGroup

CurrentGroup is a DAX function that comes with the usage of GROUPBY. CurrentGroup means the subset of the main table, which can be used for any aggregation. I used the below sample to show you what the CurrentGroup looks like. However, you cannot see that visually, this happens behind the scene.

The screenshot above is creating using Power Query Editor. I used it to show you what the Current Group looks like, so don’t look for such a visual way of looking at CurrentGroup in DAX. As you see in the screenshot above, the CurrentGroup, when our filter context is High School, is the sub-table of DimCustomer with all of the columns, but filtered only for High School.

Now, using CurrenGroup, you can write the expression as below;

The CountX expression is counting rows from the CurrentGroup function.

The CurrentGroup can be used in other iterator functions such as SUMX, AverageX etc.

Some Restrictions of GroupBy

You cannot use a Calculate function in the expression section of GroupBy.

Any aggregation should be done using the CurrentGroup function.

Table can be an expression too

When you are using the GroupBy (and many other functions) you can use a real dataset table as the input, your can use another function that produces a table (table expression). the below example, shows a scenario what we create a column in the DimCustomer table for the Revenue first, and then use that for grouping in the next step;

Sales in the above expression is a measure with the expression of Sum(FactInternetSales[SalesAmount]).

Remember the Power of Virtual Tables

If you just want to use GroupBy to create an aggregated calculated table in Power BI, Although you can do it, however, there might be a better way to do it; using GroupBy in Power Query, or in the data source. The main power of using GROUPBY or other DAX grouping functions is the ability to use them as a virtual table, which is an aggregated table created on the fly inside a measure definition. Using a virtual table, makes your DAX measure even more powerful, however, you need to be aware that with the power comes other costs too, sometimes virtual tables, might cause performance impact.

Download Sample Power BI File

Download the sample Power BI report here:

Enter Your Email to download the file (required)

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply