SWITCH or IF that groups all negative values and blanks

[CobraKAI]

Hello,
I have worked on creating a SWITCH or IF function that groups certain values into groups with a category name. I am having trouble of getting all negative values and blanks into the same group. This is what I have so far. What am I doing wrong?

SWITCH(

TRUE(),

Members[Age] >=0 && Members[Age]<=15 , "Child",

Members[Age] >15 && Members[Age]<=26 , "Youth",

Members[Age] >26 , "Adult",

Members[Age] <0 && ISBLANK(Members[Age]), "Faulty age" )

And my IF function attempt:

IF(Members[Age] >=0 && Members[Age] <=15, "Child",

IF( Members[Age] >15 && Members[Age] <=26, "Youth",

IF(Members[Age] >26, "Adult",

IF(ISBLANK(Members[Age]) && Members[Age]<0, "Faulty age","Faulty age")

)

)

)

[v-xuding-msft · Accepted solution]

Hi @CobraKAI ,

As az38 suggested, you need to change "&&" to "||". And you also should judge "Faulty age" firstly. Please try this.

Column = SWITCH(
TRUE(),
'Members'[Age] <0 || ISBLANK('Members'[Age]), "Faulty age",
'Members'[Age] >=0 && 'Members'[Age]<=15 , "Child",
'Members'[Age] >15 &&'Members'[Age]<=26 , "Youth",
'Members'[Age] >26 , "Adult",
"Faulty age" )

 

Best Regards,

Xue Ding

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

[CobraKAI]

It was so easy and small so now I feel stupid. Thanks for the help!

But why does the order of the statements matter? The first reply in this thread didnt solve my issue with blank rows but  v-xuding-msft did.

For anyone wondering about the order of the statements:

It matters because the first statement that matches the criteria get used. See this blog post:
https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/
"Aha!  So the FIRST test case that matches, gets used"

[az38]

Hi @CobraKAI 

try OR, not AND

Members[Age] <0 || ISBLANK(Members[Age])

SWITCH(
TRUE(),
Members[Age] >=0 && Members[Age]<=15 , "Child",
Members[Age] >15 && Members[Age]<=26 , "Youth",
Members[Age] >26 , "Adult",
Members[Age] <0 || ISBLANK(Members[Age]), "Faulty age",
"Faulty age" )