GroupBY in Power BI vs CURRENTGROUP
GroupBy in Power BI is used to Group columns in a table based on a certain field. It is similar to the Summarize function in Power BI, but groupby cannot do an implicit Calculate to an extension column (Extension columns are columns that are added to an existing table). But it can use a new function CURRENTGROUP(), to do the aggregations for the extension columns. Groupby function returns a table with a selected set of columns. The table which is created by the group by function can be reused which makes it highly efficient.
GROUPBY (<table>, [<groupBy_columnName1>,<groupBy_columnName1>], [<name>, <expression>])
- Table – The table where the groupby functions needs to be applied.
- groupBy_columnName – The existing columname by which the data is to be grouped. This shouldn’t be an expression
- name – the name given to the new column that is being added to the list of GroupBy columns. This should be placed with double quotes.
- Expression – A DAX expression that returns a single scalar value, where the expression is to be evaluated for each set of GroupBy values. The expression used in DAX can be of any ‘X’ aggregation functions like SUMX,AVERAGEX,MAXX,MINX,COUNTX,PRODUCTX etc.
- The value has to be returned from the DAX expression, which will have the selected columns for the groupBy_columnName arguments and the grouped by column name parameters.
GroupBy function evaluates as mentioned below:
- Takes the specified table name and all the related table as first argument
- Groups the data based on all the grouping columns specified in DAX expression, that should exist in the table specified as the first argument
- Each group will be a single result in the expression ,but will be multiple rows in the underlying table.
Example : The below ProductSales, has four columns for Bike when grouping based on Product and aggregating based on sales , the bike will have only one entry in the resultant table.
| Product | Country | Sales | Quantity |
| Bike | NewYork | 100 | 5 |
| Bike | NewYork | 150 | 10 |
| Bike | London | 100 | 4 |
| Car | London | 300 | 3 |
| Car | NewYork | 350 | 7 |
| Truck | NewYork | 450 | 2 |
| Truck | London | 450 | 1 |
| Product | Total Sales |
| Bike | 350 |
| Car | 600 |
| Truck | 900 |
GROUPBY (
ProductSales,
ProductSales [Product],
“Total Sales”, SUMX( CURRENTGROUP(), ProductSales[sales])
)
The function groups a selected set of rows into a set of summary rows by the values of one or more groupBy columnName. One row is returned for each group as shown above.
- For each group an extension column will be added. An implicit calculate won’t be performed inside the group by function and hence filter cannot be added within it.
Limitations:
CALCULATE functions are not supported within DAX, hence groupby cannot be used within measures and in directquery when used calculated columns or Row Level Security (RLS) rules.
CURRENTGROUP() function can be used only within the groupby function which will perform a table scan at the top level in expressions.
Let’s see an example below
Say, We have a table called Productsales as below, we are grouping the table based on the products and naming the column as Sales ,then the MAX sales in each category is calculated within the GroupBy function. Then we are calculating the total of maximum sales in each category which is being returned as the final result.
| Product | Sales Amount | Quantity | SubCategory |
| Televison and Video | 9194 | 10 | Sony LED TV |
| Audio | 3224 | 5 | Boat Headphone |
| Toys and Video Games | 7638 | 14 | PS3 |
| Home Appliances | 3746 | 6 | Oven |
| Entertainment | 4221 | 11 | Netflix |
| Cellphones | 4781 | 7 | Apple 10 |
| Cameras | 8349 | 9 | Sony |
| Televison and Video | 7196 | 9 | LG Video System |
| Audio | 1564 | 13 | Airpods |
| Toys and Video Games | 5456 | 5 | Soft Toy |
| Home Appliances | 2402 | 12 | Refrigerator |
| Entertainment | 4790 | 3 | Amazon Prime |
| Cellphones | 1673 | 7 | Apple X6 |
| Cameras | 4612 | 6 | Canon |
| Audio | 4496 | 8 | Bluetooth Speaker |
SalesAmount = var a =GROUPBY(ProductSales,ProductSales[Product],"Sales",MAXX(CURRENTGROUP(),ProductSales[Sales Amount])) return MAXX(a,ProductSales[Sales Amount]*ProductSales[Quantity])
Summary
GROUPBY function can be used instead of ADDCOLUMNS/SUMMARIZE whenever we want to access the rows of a group in an iterator. This is necessary whenever there is nested grouping operations. Summarize function is hard to use and have few performance issues when computing aggregated values. Also the result from groupby is reusable which makes its performance better.
For more useful blogs, please visit Learn DAX
