cancel
Showing results for 
Search instead for 
Did you mean: 

How To Create Dynamic & Custom Groupings For Your Data - Advanced DAX Technique

In this article, I get advanced with DAX and show you how to create custom dynamic grouping or segmentation. I enjoy showing the power of this formula and technique inside Power BI.  

 

Dynamic grouping is a perfect example of a valuable insight that you can extract out of your analysis in Power BI.  

 

Using DAX formulas to dynamically group or segment data in Power BI is so powerful. You’re building your data models and creating many filters and ways to group your data. Note that these segmentations don’t exist in your original datasets, so you’re actually creating new insights. 

 

Dive into some of my tutorials on dynamic grouping to learn more about this technique. 

 

The first one showcases how I create custom dynamic segmentation to rank customers based on different metrics. Watch the full video tutorial below. 

 

 

In this tutorial, the data is segmented into three groups, which enables you to see and drill into key clients, products, and other dimensions in a more effective way. With the custom grouping created, patterns in the data are clear and obvious. Without these three groups, all that data would appear as chaotic dots on a scatter chart.  

 

Since this is a dynamic calculation, the customers are not only ranked throughout the entire data set and timeline, but also for specific selections in the visualizations. 

 

2020-08-17 (2).png

 

This next tutorial that I want you to watch features a unique dynamic segmentation technique. Here, I demonstrate how to segment dimensions within your data based on any calculated result. In this example, I group the customers based on a percentage ranking, which is based on their sales. 

 

2020-08-17 (3).png

 

What’s great about this technique is that you can re-use it since it’s based on a percentage. You can apply this type of logic in so many ways.  

 

Enjoy learning this one! 

 

 

Notice that I use a secondary table or supporting table in the tutorials above, and I combine it with DAX formulas to create the grouping. 

 

Another tutorial that I want to share with you is about dynamic segmentation using dynamic parameters. I’ve seen a lot of Enterprise DNA members ask about this so many times on the Enterprise DNA Support Forum, so I thought it would be great to share it here as well. 

 

2020-08-17 (5).png

 

In this scenario, figuring out which area to segment first is a bit challenging. You always need to find a way to feed dynamic parameters into your formula. Watch the video below and see how I simplified all this with some advanced DAX techniques. 

 

 

The data that I used in this example has very low frequency; imagine the possibilities when you apply this into bigger data sets. You can also use this technique to segment other information and you can add many other parameters.  

 

Moreover, I demonstrate here how to work with measures within measures. Techniques like these will give incredible insights from your report analysis. 

 

Key Takeaway 

 

Creating custom dynamic segmentation in Power BI is a bit advanced in terms of DAX patterns and techniques. But if you can wrap your head around this, you will be able to apply this great technique to many scenarios and extract valuable insights. 

 

I have many more tutorials about dynamic grouping, so check them out on our website. I have included some of them in the related links below. Learn more about this topic and technique from the course modules below, as well.

 

Good luck on this one! 

 

Sam 

 

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

Use DAX To Segment & Group Data In Power BI 

Power BI Customer Segmentation: Showcasing Group Movement Through Time 

Dynamic Segmentation: How To Segment Customers Into Groups Using Advanced DAX 

 

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

Advanced DAX Combinations 

Unique Analytical Scenarios 

Learning Summit Series 

 

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

Dynamic Segmentation With Dynamic Parameters Video & PBIX File 

Correct Total By Dynamic Segmentation With Dynamic Parameters 

Query Based Dynamic Segmentation (NO Lookup Table In Data Model) 

For more dynamic segmentation queries to review see here…..