Multiple IF statement DAX

[sathish_kumar1]

Hi guys,

   I need to Assign values "Test -1" For values between 2500 to 3499, "Test -2 for values between 3500 to 4999" and "Test -3" for values above 5000. I used a dax expression

Test =  IF(AND([Per Bike Sale]>=2500,[Per Bike Sale]>=3499),"Test- 1",IF(AND([Per Bike Sale]>=3500,[Per Bike Sale]>=4999),"Test- 2",IF([Per Bike Sale]>=5000,"Test- 3", "NA")))

But when i use this formula i am getting null values in the Sale column with test value as "NA" but my Sale column is not havinh any null values. Please help me with it and provide an alternative solution.

[alwweb]

Another option , that I linke because it is easier to come back later and read is SWITCH.   It only works if you know the trick of adding TRUE() as the first entry since it tells it to look for the answer that is true.  You can read and see an option with switch here: https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

Also, it probably isn't a best practice, but SWITCH exits as soon as it hits the first true, so if you are careful with the order of your switch statement, you don't have to put both ends of every range.  The values that previously matched won't be considered for the later tests.  Also, you can add one final value with no partner for an else.

Looking at your code, I'm seeing a couple of things, you are using all > signs.  Shouldn't the first test be >= 2500 and <=3499?   And then the 2nd one <=4999?

[camargos88 · Accepted solution]

Are you creating a custom column ? If yes try this code:

IF('Table (3)'[Sale]>=2500 && 'Table (3)'[Sale] <= 3499; "Test-1"; IF('Table (3)'[Sale] >= 3500 && 'Table (3)'[Sale] <= 4999; "Test-2"; IF('Table (3)'[Sale] >= 5000; "Test-3";BLANK())))

Ricardo

[sathish_kumar1]

Hi @camargos88 ,

          No i am creating a measure and assigning the test values. I cannot create a custom column because i am using Direct Query.

The query you shared is  working. Thanks