Group age by bracket direct query

[achristopherson]

I have a dataset with names and ages. I need to show three age groups: Under 18, 18-64, 65 and above. Can someone give me some pointers for setting this up? It seems like it should be relatively simple but none of the things I've tried work. I tried creating a conditional column but it won't work in direct query mode. I also tried a DAX formula but it kept giving me an error. 

AgeGroups = IF(FamilyMember[Age] < 18, "1) Under 18",If (FamilyMember[Age] <=17 and FamilyMember[Age] >=65),"2)18 -65", If (FamilyMember[Age] >= 65,"3) Over 65",))

[v-lionel-msft · Accepted solution]

Hi @achristopherson ,

You can create a column:

Column = 
SWITCH(
    TRUE(),
    FamilyMember[Age] < 18, "1) Under 18",
    FamilyMember[Age] >= 18 && FamilyMember[Age] < 65, "2)18 -65",
    "3) Over 65"
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Try:

AgeGroups = 
  SWITCH(TRUE(),
    FamilyMember[Age] < 18, "1) Under 18",
    FamilyMember[Age] >= 65,"3) Over 65",
    "2)18 -65"
  )

[Mariusz]

Hi @achristopherson 

Try these two patterns.

https://www.daxpatterns.com/dynamic-segmentation/

https://www.daxpatterns.com/static-segmentation/

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn