Forum Discussion
DAX formula help for multiple IF statements
Hi i am trying to create banding for with the following statement;
if [NumberOfUsers] <250 then "SME" else if [NumberOfUsers] >=250 <=1000 then "Corporate" else if ([NumberOfUsers] >=1000 <=5000 then "Enterprise" else if [NumberOfUsers] >=5000 then "Global" else "null"
What am i doing wrong? as all i reiceve is ERROR? any help would be great
8 Replies
jthomsonSolution Sage
You'd need to put && in your formulas in the right places to say it's both >=250 and <=1000 etc, but that whole >=250 is redundant, as anything less than 250 has already been called SME. Try nesting them - ask if it's less than 250, then go SME if true and your next if statement (checking if <1000) otherwise
CrisYanResolver III
Then my formula works.
The SWITCH(TRUE()) statement checks conditions in order, and stops if one condition is TRUE. So if number = 249, the formula will stop on the first statement and will return "SME", but if number = 250 it will skip the first statement and will stop on the second one, returning "corporate" (because 250< 1000).
- CrisYan
The syntax for IF in DAX is:
IF(CONDITION ; RESULTIFTRUE ; RESULTIFFALSE)For multiple IF statements I recomend SWITCH(TRUE())
Measure = SWITCH(TRUE(); [NumberOfUsers] < 250; "SME"; [NumberOfUsers] < 1000 ; "Corporate"; [NumberOfUsers] < 5000 ; "Enterprise"; [NumberOfUsers] >= 5000 ; "Global"; BLANK())
Be aware of your limits ¿[NumberOfUsers] = 1000 is Corporate or Enterpise?.
Regards!