Dynamic Age Grouping

DAX DAX Calculations
1

Hello team

I am trying to create a dynamic age group based on total of registration Event[Event Type] = “FINISH|REGISTER” to show a pattern of age group who end up registering to the app.

I have created a column for age grouping

Age Group =

SWITCH(TRUE(),

User[User Age] >=0 && User[User Age] <25, “Age 0-24”,

User[User Age] >= 25 && User[User Age] < 50, “Age 25-49”,

User[User Age] >=50 && User[User Age] <75,“Age 50-74”,

User[User Age] >= 75 && User[User Age] < 100,“Age 75-100”,

“NA”

)

I have already measure to calculate the total of users who finish registration.

Finish Registration =
CALCULATE( COUNTROWS( ‘Fact web Analytics’ ) ,
FILTER( Event ,
Event[Event Type] = “FINISH|REGISTER” )
)

Thank you so much for all the help.

2

Hello @iasma,

Thank You for posting your query onto the Forum.

Well it’s quite a easy process. We’ve quite a lot videos created on this topic which is also available onto our education portal as well as onto the YouTube channel.

I’m providing few of the links below of the videos as well as blogs. Also do checkout our education portal for more content on this.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

https://medium.com/@sam.mckay/dynamically-group-and-segment-data-using-dax-in-power-bi-ba7e08e507ec

3

Thanks @Harsh you are a hearo.

I was watching same videos earlier and I was struggling to apply these to my data.

4

Hello @iasma,

Firstly, I would really like to say a “Thank You” to you for the compliment. :slightly_smiling_face:

Moving on to the analysis, I’m sad to say but none of the User ID’s from the User table matches with Fact Web Analytics table. I came to know about this when I applied the VLOOKUP formula into the excel file in order to find out why it was showing blanks. Below is the screenshot provided for the reference -

Age Group Distribution

Now, when I dragged the “Close Registation” formula which we’d created in the previos post it showed blanks. As per the screenshot provided below -

Errorneous Result

So now, at first, I decided to check my formula whether that contained the error or not. But it turned out that “none” of Users ID’s between both these tables matches and these led to blank/errorneous results.

So now to check I added just one row to the Fact Web Analytics table by copying the User ID from the User Table and it gave the correct result for that particular row. Below is the screenshot provided for the reference -

Cross - Check Results
Cross - Check Results676×558 9.51 KB

So if you observe the screenshot provided above, you’ll see that only 1 has the correct result and that too it’s the row which I’ve added for checking purpose.

I’m also also attaching the working of my Excel as well as PBIX file for the reference.

I request you to check the data between the User table and Fact Web Analytics table. Once you’ve got the match between the User ID’s from both the tables then you can definitely apply the solution that I’ve provided into this file.

Hoping you find this useful and helpful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Data - Harsh.xlsx (518.3 KB)

Data Checking - Harsh.pbix (359.4 KB)

3 Likes
5

Hi @iasma, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

6

This solved the issue.

Thank you so much @Harsh

7

Hello @iasma,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh