Switch() using multiple conditions within same ID

[KayCon]

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.

id	Name	Lead Name	Age	Is Adult	Is Child
123	Name1	Name1	31	1	0
123	Name2	Name1	27	1	0
123	Name3	Name1	75	1	0
322	Name4	Name4	4	0	1
322	Name5	Name4	25	1	0

[FreemanZ]

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]

That worked perfectly, thank you for your help 🙂