cancel
Showing results for 
Search instead for 
Did you mean: 
EnterpriseDNA

Power BI Group By Using DAX & Power Query

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.  

 

2021-07-05 (5).png

 

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. 

 

2021-07-05 (10).png

 

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. 

 

All the best! 

 

Sam 

 

 

***** Related Links ***** 

GroupBy In Power BI: An Implied Function In VertiPaq 

Use DAX To Segment & Group Data In Power BI 

Organizing Your Queries Into Groups In Power BI 

 

***** Related Course Modules***** 

Power Query Series 

Mastering DAX Calculations 

DAX Formula Deep Dives 

 

***** Related Support Forum Posts ***** 

Grouping Products / Customers 

Advanced Table Functions: GROUPBY 

GROUPBY Or SUMMARIZE 

For more group by function support queries to review see here…. 

 

 

 

Comments

Hi,

 

Can you please tell me if we do group by in Power Bi using DAX or in Edit Query(Group BY option), is it create the virtual table?