Forum Discussion
IF statement for banding numbers
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
Anonymous
Some clarification:
In Query Editor you can use my previous code and in DAX you can use Thejeswar code.
Br,
T
7 Replies
- Thejeswar
Super User
Hi Anonymous,
Use the below code
IF([NumberOfUsers] < 250, "SME", IF([NumberOfUsers] >= 250 && [NumberOfUsers] <= 1000, "Corporate",IF([NumberOfUsers] > 1000 && [NumberOfUsers] <= 5000, "Enterprise","Global")))
- Thejeswar
Anonymous wrote:got Token RightParen expected error on both of your solutions whats the reaosn for this? thank you
This is likely because you are having an additional paranthesis (which is not expected) or missed a paranthesis in either side of your code.
Check if you have properly closed all your IFs
Anonymous
Some clarification:
In Query Editor you can use my previous code and in DAX you can use Thejeswar code.
Br,
T
- vanessafvg
Community Champion
i think the switch statement will be more effective and easier to read
https://msdn.microsoft.com/en-us/query-bi/dax/switch-function-dax
example take from https://powerpivotpro.com/2012/06/dax-making-the-case-for-switch/
Fund Size:=SWITCH(TRUE(),
AND([Fund Balance]>=0, [Fund Balance]<=10000), “Up to $10,000”,
AND([Fund Balance]>=10001, [Fund Balance]<=50000), “$10,001 to 50,000”,
AND([Fund Balance]>=50001, [Fund Balance]<=100000), “$50,001 to 100,000”,
AND([Fund Balance]>=100001, [Fund Balance]<=500000), “$100,001 to 500,000”,
AND([Fund Balance]>=1500001, [Fund Balance]<=1000000), “$500,001 to 1,000,000”,
“greater than $1,000,000”
)
