User table instead of multiple IF for cohort second table.

[stuf1968]

First, as with many others here - I'm relatively new to PBI and DAX, and my books haven't arrived from Indigo/Chapters yet.  

I have created a data table (tAgeCohort) for min/max of age range for age cohort

ID   MinAge    MaxAge   AgeRange

1      20             29              20-29

2      30             39              30-39

...

5      70            79               70-79

6      80            125               80+

I have another table that contains member information  z_TTL_Mbr  (contains 150k rows)

MemberID         Age

10000                 28

12434                 65

88833                 48

Instead of using a calculated column with multiple IF statements. I'd like to know how to build a Measure or DAX to look up the z_TTL_Mbr[Age] and assign the appropriate AgeRange from tAgeCohort.

Thank you for your assistance in this 'newbie' question.

[MalS]

Have you considered using a Group instead?

In the member information table, click on the Age field, then on the ribbon click Modelling > New Group. Set Group Type to List, and then create your cohort groups that way. This will create a new field that can be used in visualizations, filters, etc.

[stuf1968]

Thanks @MalS, I have considered that method and have used it before.  I'm hoping that someone can instruct me on how to solve this request using DAX or by a measure... I have other elements that need similar resolution. I picked this example as it's the easiest to explain.

Suggestions?

[magsod]

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

[stuf1968]

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-msft · Accepted solution]

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

[stuf1968]

Thank you @v-caliao-msft.