Dynamic Banding or Grouping in Power BI – Using DAX Measures – Choose the Size of Bins

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

In the previous article/video, I explained how to create dynamic bins by choosing the count of bins in a slicer in the Power BI report. In this article, I’ll explain, how you can do it the other way around, which is by selecting the size of the bin, you will have bins and buckets dynamically generated.

Banding, Grouping or Binning

Banding (or grouping or binning), means splitting a numeric data type into buckets or groups. Let’s say, we have customers with all ages, but we want to group them in age buckets of 10. one bucket would be 1 to 10, another from 11 to 20, etc. Here is an example of those buckets in the X-Axis of a chart;

Banding can be done statically using any of the methods below;

Sample Dataset

I have a DimCustomer table with a few columns about each customer, and one of the columns is Age. If you are interested to learn how the age is calculated, read my article here about calculating the age in Power Query.

 

Desired Dynamic Banding

However, sometimes, you need the banding to be dynamic. Dynamic banding means that user can select (through a slicer) how many buckets (or bins or bands) is needed, and then the calculation changes based on that. Here is what we need:

I have also explained how you can implement dynamic banding by the count of bins in the previous article, which you can read about it here:

Dynamic Banding or Grouping in Power BI – Using DAX Measures – Choose the Count of Bins

Most of the process is similar to the previous article, but because this can be a need on its own, I have added that separately here in full details.

Age Band Table

This solution is only one of the methods for implementing this scenario. There are, of course, other methods too. In this method, First I have strated with creating an Age Band table with all possible age values.

This table is then related to the DimCustomer using the Age column;

The relationship above means that I can have Age from the Age Band table as the X-Axis to filter the count of customers in the DimCustomer table;

Now for the dynamic banding, here is the list of steps:

Size of Bands; What-If Parameter

Create a What-if parameter for the size of bands,

the what-if parameter to start from the minimum size of band you want (let’s say 1), to maximum (let’s say 120), and the increment value (let’s say 1). That would create a table like this:

this is how the table looks like:

To learn more about the GENERATESeries function in DAX, read my article here.

When you add the what-if parameter, you will also have a measure using the SELECTEDVALUE as below;

This what-if parameter is now a slicer on your page.

DAX Measures

You can add a few measures for helping with the final calculation, or combine them all in one measure. I split it here, because it helps the process of learning. Here are my measures:

Min Age is the measure that shows the minimum age value;

Max Age is showing the maximum age value;

Band Count Value is a measure that calculates what is the count of the bands, based on the size of the band, and minimum and maximum value; (the count of bands is not needed for the main calculation, it is just added here for extra information);

For example, if the band size (What is Band Size) in the slicer is selected 3, then the count of bands is (120-0)/3=40.

Count of Customers in Each Band

This is the measure that does the main function of our dynamic banding. This measure’s expression first finds out what is the band of the current age in the X-Axis, and calculates the count of customers for that band, but only shows the value for the band’s head (which is the age that the band starts from). Here is the expression:

To explain parts in this expression, here are the details:

_AgeBandTable is a virtual table of all bands, this is created dynamically;

_currAge is giving us the Age value in the X-Axis:

The _bandHead calculation is a filter of the _ageBandTable to find out the band for the selected Age;

Based on the _bandHead and size, now the Tail is calculated:

The return value is the count of rows from DimCustomer when the age value is between head and tail;

Altogether, this measure returns the count of customers in each age band that is dynamically selected by the slicer;

And you can test the final results here. As you see, the user can dynamically choose how many bands are needed, and everything changes based on that;

Until the next DAX article, have fun DAXing!

Download Sample Power BI File

Download the sample Power BI report here:

Enter Your Email to download the file (required)

Video

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
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

Your email address will not be published. Required fields are marked *