Forum Discussion
User table instead of multiple IF for cohort second table.
Hi stuf1968,
You need to have a consecutive age number in your tAgeCohort, and set age range for this age. Then you can get age range in z_TTL_Mbr table by using age value.
Create a table with a column ID have values from 1 to 125. Then create three calculated columns.
AgeRange = if(and(tAgeCohort[ID]<=29,tAgeCohort[ID]>=20),"20-29",if(and(tAgeCohort[ID]<=39,tAgeCohort[ID]>=30),"30-39",if(and(tAgeCohort[ID]<=49,tAgeCohort[ID]>=40),"40-49",if(and(tAgeCohort[ID]<=59,tAgeCohort[ID]>=50),"50-59",if(and(tAgeCohort[ID]<=69,tAgeCohort[ID]>=60),"60-69",if(and(tAgeCohort[ID]<=79,tAgeCohort[ID]>=70),"70-79",if(tAgeCohort[ID]>=80,"80+","UNK")))))))
Min = CALCULATE(MIN(tAgeCohort[ID]),ALLEXCEPT(tAgeCohort,tAgeCohort[AgeRange]))
Max = CALCULATE(MAX(tAgeCohort[ID]),ALLEXCEPT(tAgeCohort,tAgeCohort[AgeRange]))Then create a caluclated column in z_TTL_Mbr table.
Age Group = LOOKUPVALUE(tAgeCohort[AgeRange],tAgeCohort[ID],z_TTL_Mbr[Age])Then you can use this column in your slicer or visual.
Regards,
Charlie Liao
If you want to use the custom age groups to slice or group the data in your visuals, you will need to either create a calculated column in your table z_TTL_Mbr to hold the age groups. Or create a relationship between that table and your table that holds the age groups.
If you go with the relationship you need to structure the table so that every age gets a separate row in order for the relationship to be created.
Age AgeRange
20 20-29
21 20-29
22 20-29
...
80 80+
etc...
Br,
Magnus
Perhaps I can state this differently. :)
I have 2 tables: tAgeCohort (w/ ID, min, max, and range)
z_TTL_Mbr[Age] (w/ details of the individual member, unique IDs, and of course age)
I have created a calculated column in z_TTL_Mbr with multiple nested IFs in order to manually create the range that member falls into.
Age Group = if(and(z_TTL_Mbr[Age]<=29,z_TTL_Mbr[Age]>=20),"20-29",if(and(z_TTL_Mbr[Age]<=39,z_TTL_Mbr[Age]>=30),"30-39",if(and(z_TTL_Mbr[Age]<=49,z_TTL_Mbr[Age]>=40),"40-49",if(and(z_TTL_Mbr[Age]<=59,z_TTL_Mbr[Age]>=50),"50-59",if(and(z_TTL_Mbr[Age]<=69,z_TTL_Mbr[Age]>=60),"60-69",if(and(z_TTL_Mbr[Age]<=79,z_TTL_Mbr[Age]>=70),"70-79",if(z_TTL_Mbr[Age]>=80,"80+","UNK")))))))
I could use the group ability - but as the data for z_TTL_Mbr is dynamic, if the age isn't already in the list at the time I create the group I would have to continually go back into the table to make changes to the group listing. Can't use the automatic group bin feature because the min/max ages are fixed across all organization reports.
So I really am looking for a way to have tAgeCohort[AgeRange] resolve for z_TTL_Mbr[Age] so I can use it for slicer and visuals.
Thanks.
v-caliao-msftMicrosoft Employee
Hi stuf1968,
You need to have a consecutive age number in your tAgeCohort, and set age range for this age. Then you can get age range in z_TTL_Mbr table by using age value.
Create a table with a column ID have values from 1 to 125. Then create three calculated columns.
AgeRange = if(and(tAgeCohort[ID]<=29,tAgeCohort[ID]>=20),"20-29",if(and(tAgeCohort[ID]<=39,tAgeCohort[ID]>=30),"30-39",if(and(tAgeCohort[ID]<=49,tAgeCohort[ID]>=40),"40-49",if(and(tAgeCohort[ID]<=59,tAgeCohort[ID]>=50),"50-59",if(and(tAgeCohort[ID]<=69,tAgeCohort[ID]>=60),"60-69",if(and(tAgeCohort[ID]<=79,tAgeCohort[ID]>=70),"70-79",if(tAgeCohort[ID]>=80,"80+","UNK")))))))
Min = CALCULATE(MIN(tAgeCohort[ID]),ALLEXCEPT(tAgeCohort,tAgeCohort[AgeRange]))
Max = CALCULATE(MAX(tAgeCohort[ID]),ALLEXCEPT(tAgeCohort,tAgeCohort[AgeRange]))Then create a caluclated column in z_TTL_Mbr table.
Age Group = LOOKUPVALUE(tAgeCohort[AgeRange],tAgeCohort[ID],z_TTL_Mbr[Age])Then you can use this column in your slicer or visual.
Regards,
Charlie Liao