SWITCH function failing wiht measures

[Anonymous]

Hello community, I hope you can help me with this.

I am working with a SQL Database into my Power BI desktop, in which I have 'Deal ID' and 'Amount', and I am trying to create a custom column to categorize those deals by 'Deal Band'.

After trying many times with nested IF and SWITCH, I can't still find the right way to make it work. Also,  Conditional Column is not letting me the 'between' option for measures, and it doesn't work either.

Here is the code that I am using:

[Deal Band]:=

SWITCH(
       TRUE(),
  [Amount]<=5000,”<5K″,
  [Amount]<=10000,”5-10K″,
  [Amount]<=20000,”10-20K”,

  [Amount]<=50000,”20-50K″

  [Amount]<=100000,”50-100K″,

  [Amount]<=500000,”100-500K″,

  [Amount]<=1000000,”500K-1M″,

  “1M+”)

Can somebody help me understand what am I doing wrong?

Also, would it be possible to have an improved version of nested IF predefined with ranges for values?

Thank you very much in advance.

Pablo

[Danke1981]

Another solution would be:

Grouping the amount first

                                                                  Group

[Amount]<=5000,                                       "1"
5000<[Amount]<=10000,                           "2"
10000<[Amount]<=20000,                         "3"

20000<[Amount]<=50000,                         "4"

.

.

.

Then use swith function:

WITCH(
       TRUE(),
  [Group]=1 ,”<5K″,
  [Group]=2 ,”5-10K″,

.

.

.

.

[Ashish_Mathur]

Hi,

What problem are you facing?

[riyuk11]

Hi,

i am also facing same problem. when i am trying write the column name after TRUE() function, it is not showing me any table/column name. please help

[Anonymous]

Hello @Ashish_Mathur

After trying both 'import' and 'direct query', I have the same error when I apply the formula: 

The following syntax error occurred during parsing: Invalid token, Line 1, Offset 32, ”

This is the exact formula that I use:

Measure = SWITCH(TRUE(),
vw_PRMCancelled[Amount]<=5000,”<5K″,
vw_PRMCancelled[Amount]<=10000,”5-10K″,
vw_PRMCancelled[Amount]<=20000,”10-20K”,
vw_PRMCancelled[Amount]<=50000,”20-50K″,
vw_PRMCancelled[Amount]<=100000,”50-100K″,
vw_PRMCancelled[Amount]<=500000,”100-500K″,
vw_PRMCancelled[Amount]<=1000000,”500K-1M″,
“1M+”)

Any help will be appreciated.

Thanks

[MattAllington · Accepted solution]

Banding is done in columns, not measures. Read my 2 articles here

https://exceleratorbi.com.au/banding-in-dax/

https://exceleratorbi.com.au/conditional-columns-power-bi-desktop/