Forum Discussion

stuf1968's avatar
stuf1968
Frequent Visitor
9 years ago
Solved

User table instead of multiple IF for cohort second table.

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

 

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.

 

:smileyvery-happy:

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    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

     

6 Replies

  • MalS's avatar
    MalS
    Icon for Resolver III rankResolver III
    9 years ago

    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's avatar
      stuf1968
      Frequent Visitor
      9 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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