Age Banding in Power BI Using TREATAS DAX Function – Relationship Based on Between

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

One of the most common types of banding or binning is banding based on a range. Let’s say, for example,  you want to have a group of customers based on their age group. The age group banding can be created in Power Query at the data transformation stage. It can be created using the Grouping and Binning option in Power BI, or it can be even created using DAX measures. If you use a DAX measure for the banding, then TREATAS can be a useful function for implementing it. In this post, I’ll explain how it works.

If you are New to TREATAS

If you haven’t use TREATAS before and are new in using this function, I do recommend reading/watching the first two articles that I explained the way that TREATAS works;

Building a Virtual Relationship in Power BI – Basics of TREATAS DAX Function

Creating Relationship Based on Multiple Fields in Power BI Using TREATAS DAX Function

Sample Model

I am using a very simple data model in this example, the table below is what I use as the Sample Data;

The goal is to have an age group banding for customers and get a count of customers in each group. something similar to this:

Other Methods for Banding

You can use Power Query with a conditional column to create banding, or you can use grouping and binning option in Power BI to achieve the same. Here, in this article, however, I am going to explain how that is possible through a measure using the TREATAS function.

Age Band Table

As we need the banding to be the axis of the chart, we need that as a field, You can create an age band table using What-If parameters, using the GenerateSeries function, or simply using an expression like this:

here is how the Age Band table looks like:

when you see 10 as the band up there, it means from 1 to 10, when you see 20, it means from 11 to 20 and so on.

This table shouldn’t have a relationship with the Sample Data table, because if you create the relationship, then it would only filter data for the top value of each band. so the tables remain unrelated. like a normal way of using a What-if parameter table.

DAX Measure using TREATAS

Now, using a measure like below, we can get the count of people in each band;

The expression can be split into multiple sections. First is the variable that fetches the current age band (the age band in the visualization’s filter context);

Then the next variable is a list (table) of values from the selected age band minus nine, to the value itself, increasing one at a time. for example; if age band value is 40, this list would be from 31 to 40: 31, 32, 33, …., 40.

Now that we have a list of possible age values for this band, we can use that to filter the Sample Data table using TREATAS;

Altogether, this works like a scenario that you have created a relationship between the Age Band table and the Sample Data but on a BETWEEN condition, not an exact equal condition.

This example shows a really interesting use case for TREATAS, which is creating a relationship based on not-equal criteria. Relationships in Power BI are on the basis of equality of values, you cannot create a relationship that says this value should be less than or equal, or between or anything like that of the other value in the other table. However, using TREATAS combined with other functions, you can do that. I’ll write about this design pattern separately later in detail.

Age Bands with Start and End

If we step beyond the basic example, we can even create a bit more advanced banding. One of the limitations of grouping and binning in Power BI is that bins should be all of the equal size. For example, all age bands should be of 10 years, or all 5 years, you cannot say some are smaller than the others. Using your own Age Band table, however, you can define exactly what you want. Here is another detailed Age Band table;

and the table looks like this:

As you see, I have bands which are only covering five years (31-35), and bands that are covering ten years (11-20).

DAX Measure For Custom Bands

The DAX calculation is very similar to the previous one, the only difference is that we do not need to go nine years back, we have the start and end, and can generate the period using those;

The result is as below;

Creating the Relationship Based on Between

Age banding and grouping here was just an example to show the main pattern. The pattern is whenever you want to create the relationship between two tables based on a condition that is not Equal, but it is between, how you can do it.

The trick to do this pattern of creating a relationship based on between criteria is to use a function such as GenerateSeries to build a list of possible values between the two ends of each band, and then use it in TREATAS (or any other filter functions) to filter the value in the other table. That is what you see highlighted in the expression above.

Download Sample Power BI File

Download the sample Power BI report here:

Enter Your Email to download the file (required)

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
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