Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

SWITCH function failing wiht measures

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

Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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

    • riyuk11's avatar
      riyuk11
      Regular Visitor
      8 years ago

      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

       

       

  • Danke1981's avatar
    Danke1981
    New Member
    7 years ago

    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″,

    .

    .

    .

    .