Forum Discussion
Grouping by age
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:
Community Champion
Anonymous
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! :smileyhappy:
It allows to to enter the expression but when trying to show a visual all counts are equal?
- Phil_Seamark
Microsoft Employee
Hi Anonymous
Do you have a relationship between the 'Age Profile Ranges' table and 'Current' ?
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:
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.
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.
