Forum Discussion

KayCon's avatar
KayCon
Frequent Visitor
2 years ago

Switch() using multiple conditions within same ID

Afternoon All,

 

I am trying to calulate a column to group these ID groups into the correct zones. I've made a dummy table to try and explain what I mean. All have to be within the same ID.

Zone A - Has child under 4 within same ID group

Zone B - Has child between 16-18 within same ID group 

Zone C - Anything else

 

I know how to do a simple switch(,True() command however I cannot figure out how to check if any of the ID group contains these conditions. Please if anyone could help it would help me so much.

idNameLead NameAgeIs AdultIs Child
123Name1Name13110
123Name2Name12710
123Name3Name17510
322Name4Name4401
322Name5Name42510
DAX

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

    hi KayCon 

    try to add a calculated column like:

    Zone = 
SWITCH(
    TRUE(),
    COUNTROWS(
        FILTER(
            data,
            data[id]=EARLIER(data[id])
                &&data[age]<=4
                &&data[Is Child]=1            
        )
    )<>0,    
    "Zone A",
    COUNTROWS(
        FILTER(
            data,
            data[id]=EARLIER(data[id])
                &&data[age]<=18
                &&data[age]>=16
                &&data[Is Child]=1            
        )
    )<>0,    
    "Zone B",
    "Zone C"
)

    it worked like:

    • KayCon's avatar
      KayCon
      Frequent Visitor
      2 years ago

      That worked perfectly, thank you for your help 🙂