In this blog, I’m going to show you Power BI GROUPBY using DAX and using Power Query. I’ll share a couple of Enterprise DNA tutorials; one is on how to use the GROUPBY function in a DAX measure and the other is on how to use the Power Query Group By feature.
Group By is an underrated function and feature in Power BI that lets you solve common problems in the simplest way possible. It’s the best option when you have a series of attributes that you want to group and create an aggregate.
The GROUPBY DAX function enables to group certain dimensions in your data and creates a table, either a physical or virtual, based on the elements in your data model. You’ll see in this first tutorial how I’ve used GROUPBY with the new function CURRENTGROUP.
In this example, I’ve worked out some unique insights per product by state. I did this by calculating the maximum sale amount per product per state for a particular customer. It’s hard to achieve that analysis without doing some sort of aggregation within the iterating function, and so GROUPBY is a great way to do this.
Watch the full video tutorial below.
If using DAX functions and codes are too complex for you, you can use the Group By feature in Power Query as an alternative. It helps generate valuable data for complex problems easily. With Group By in Power Query, you can group values in different rows into a single value.
Take a look at this next tutorial and learn how to unleash the power of the Group By with All Rows in Power Query.
You can see through the examples in this tutorial the power of Group By in Power Query works especially when you combine it with the ability to either return the aggregate or return all rows. It gives you flexibility to tackle a wide range of group by problems.
Key Take Away
In these two tutorials, you’ve seen how to do some groupings in your data with the GROUPBY function as well as using the Group By feature in Power Query.
The GROUPBY function is similar to the table functions, SUMMARIZE and SUMMARIZECOLUMNS, but it has some unique distinctions to it that are worth exploring. Group By in Power Query is a highly useful as well. It has a great potential in creating intuitive reports and visualizations.
I hope you find this post helpful. I recommend that you try the techniques features in the tutorials and maximize the potential of the Power BI Group By.