Forum Discussion

PBIfanatic's avatar
PBIfanatic
Icon for Helper V rankHelper V
6 years ago
Solved

FILTER used for age classification

Hi,

 

I am trying to get the below logic to work. The idea is to create an age group for the employees like 30-40, 40-50 etc. 

I created an isolated table - Age Group with the groups I need.

 

Now I need to map the grouping based on the age field in my EMployees table. So I create a Age Group column (new column)

However I am unable to use the column Customer Age column I created.  This is all in the Employees table.

 

 

Age Group =
CALCULATE (
VALUES ( AgeGroups[Age Group] ),
FILTER (
AgeGroups,
Sales[CustomerAge] >= AgeGroups[Min]
&& Sales[CustomerAge] < AgeGroups[Max]
)
)

 

 

Need Help

8 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    This calculated column formula in the Sales table should work

    Age Group = CALCULATE(VALUES(AgeGroups[Age Group]),FILTER(AgeGroups,EARLIER(Sales[CustomerAge])>=AgeGroups[Min]&&EARLIER(Sales[CustomerAge])<AgeGroups[Max]))

    If it does not work, then share the link from where i can download your PBI file.

    Hope this helps.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Age Group =
  MAXX(
    FILTER('AgeGroups',
      'Sales'[CustomerAge] >= 'AgeGroups'[Min] &&
        'Sales'[CustomerAge] < 'AgeGroups'[Max]
    ),
    'AgeGroups'[Age Group]
  )
    • PBIfanatic's avatar
      PBIfanatic
      Icon for Helper V rankHelper V
      6 years ago

      Hi Greg_Deckler ,

       

      I dont get the option to select 

      'Sales'[CustomerAge]

      I can only choose the measures (from intellisense), the calculated column Customer Age doesnt appear.