DAX formula help for multiple IF statements

[Anonymous]

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

[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!

[jthomson]

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

[Anonymous]

but as i am looking to do the following anything 1-249 is SME, anything 250-999 is Corporate, anything 1000-4999 is Enterprise and anything over 5000 is Global 

[CrisYan]

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).

[Anonymous]

am getting an error when doing this ;

The syntax for ';' is incorrect. (DAX(SWITCH(TRUE();[NumberOfUsers] < 250 ; "SME";[NumberOfUsers] < 1000 ; "Corporate";[NumberOfUsers] < 5000 ; "Enterprise";[NumberOfUsers] >= 5000 ; "Global";BLANK)).

[CrisYan]

Replace all ';' for ','.

(My PBI Desktop are with ';', yours must be with ',')

[Anonymous]

is there something i am missing to get this error; 

The value for 'userband' cannot be determined. Either 'userband' doesn't exist, or there is no current row for a column named 'userband'.

[v-juanli-msft]

Hi @Anonymous

Use this formula

measure =
IF (
    [NumberOfUsers] < 250,
    "SME",
    IF (
        [NumberOfUsers] >= 250
            && [NumberOfUsers] <= 1000,
        "Corporate",
        IF ( [NumberOfUsers] > 1000 && [NumberOfUsers] <= 5000, "Enterprise", "Global" )
    )
)

I can't see 'userband' from all above, where do you use 'userband'?

Best Regards

Maggie