Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
johnbradbury
Helper III
Helper III

Grouping by age

Morning all,

I thought I'd get a bit of time in Power BI this lovely Saturday morning. I clearly live the rock and roll lifestyle 😉

 

I have a list of queries for which I have created a measured column to calculate how old they are in days. What I'd like to do now is create a seperate column which categories them into 4 age brackets:

 

0-5 Days

6-20 Days

21-50 Days

>50 Days

 

All help and assistance appreciated.

 

 

 

 

10 REPLIES 10
OwenAuger
Super User
Super User

Hi @johnbradbury

 

Static Segmentation is probably the way to go, if you want to assign each row of your table to a group:

http://www.daxpatterns.com/static-segmentation/

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks. That looks like exactly what I need.

 

Struggling to get it working though.

 

I have created a new table: Age Profile Ranges

 

Age Range Name

Max

Min

 

I then create a new calculated column under my existing table 'Current' and I'm trying to amend this expression to suit:

 

[Price Range] =
CALCULATE (
    VALUES ( Ranges[Price Range] ),
    FILTER (
        Ranges,
        Sales[Price] >= Ranges[Min Price]
        && Sales[Price] < Ranges[Max Price]
 
So VALUES ( Ranges[Price Range] would be 'Current'[Incident Age (Days]? This is the column that calculates the age of an incident.
 
Sales[Price] would be 'Current'[Incident Age (Days]?
Ranges[Min Price] would be 'Age Profile Ranges'[Min]
Sales[Price] is that the age again?
Ranges[Max Price] would be 'Age Profile Ranges'[Max]
 
Sorry to be a pain.

 

@johnbradbury

It seems you have it right? Are you getting an error or...?

Age Range =
CALCULATE (
    VALUES ( 'Age Profile Ranges'[Age Range Name] ),
    FILTER (
        'Age Profile Ranges',
        'Current'[Incident Age (Days)] >= 'Age Profile Ranges'[Min]
            && 'Current'[Incident Age (Days)] < 'Age Profile Ranges'[Max]
    )
)

Alternatively you can create a COLUMN in your 'Current' table and use a SWITCH to create your groups like this...

Age Range ALT =
SWITCH (
    TRUE (),
    'Current'[Incident Age (Days)] >= 0
        && 'Current'[Incident Age (Days)] <= 5, "0-5 Days",
    'Current'[Incident Age (Days)] > 5
        && 'Current'[Incident Age (Days)] <= 20, "6-20 Days",
    'Current'[Incident Age (Days)] > 20
        && 'Current'[Incident Age (Days)] <= 50, "21-50 Days",
    ">50 Days"
)

That should do it also! Smiley Happy

 

This post totally helped me group employee date of birth info into years for Generational analysis. Thank you! I had to calculate the date of birth for aging purposes, but for the year of birth...this was awesome.

It allows to to enter the expression but when trying to show a visual all counts are equal?

 

Capture1.PNGCapture2.PNGCapture3.PNG

Never mind... it was me 😉

 

However it would be useful if I could sort the order:

 

0-5

6-20

21-50

50>

 

So I can see a graduated colour change:

Capture4.PNG

Hi @johnbradbury,

 

Currently, there is no such a 'sort by legend' option supported in chart visual, so we are not able to customize the order of legend. However, as a workaround, you could create Measures for each type of Days, then place each Measure in the Values area in the order you wish. Reference: Rearrange Pies in Pie Chart

 

1.PNG2.PNG

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi. I was going through the discussions and came across this one which I thought will be a lot easier: 

Anonymous
Not applicable

If you create a column on your 'Age profile Ranges' table that represents the order you want for each segment, let's say we call it Position and then create a calculated table with two columns, segments and Position, you can define the segments column to sort based on the Position column. This calculated table will be your dimension table that you can relate to your fact table.

 

Table = SUMMARIZE('Age Profile Ranges';'Age Profile Ranges'[Age Range Name];'Age Profile Ranges'[Position])

 

You can make the Postion column invisible to the client tools.

 

 

Hi @johnbradbury

 

Do you have a relationship between the 'Age Profile Ranges' table and 'Current' ?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.