The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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.
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"
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" )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
82 | |
76 | |
46 | |
39 |
User | Count |
---|---|
135 | |
109 | |
70 | |
64 | |
55 |