Forum Discussion
SWITCH or IF that groups all negative values and blanks
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?
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.
3 Replies
- az38
Community Champion
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" ) 
v-xuding-msftCommunity Support
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.
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"
