Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CobraKAI
Frequent Visitor

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?

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")
)
)
)
1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community 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" )

 2.PNG

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.

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.

View solution in original post

3 REPLIES 3
v-xuding-msft
Community Support
Community 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" )

 2.PNG

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.

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  

 

For anyone wondering about the order of the statements:

az38
Community Champion
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" )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.