cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User I
Super User I

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!

Connect on Twitter
Connect on LinkedIn
Highlighted

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.
Highlighted

 

@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

 

Highlighted

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

 

Capture1.PNGCapture2.PNGCapture3.PNG

Highlighted

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!

Highlighted

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

Highlighted
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.

 

 

Highlighted

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.
Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors