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

     

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

    • stuf1968's avatar
      stuf1968
      Frequent Visitor
      9 years ago

      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's avatar
        v-caliao-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        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