Forum Discussion

Irwin's avatar
Irwin
Icon for Helper IV rankHelper IV
4 years ago
Solved

If with multiple values // changing data to 0,1 or 2

Hi Guys,

 

I would like to create a new coloum where I change my data to three values. Either 0 (=Blanks) or 1 (=everything between 0-100) or 2 (=everything above 100).

 

I have tried if statements but only 3 arguments are possible here. 

 

Any suggestions ? 🙂

 

Need Help
  • jppv20's avatar
    jppv20
    4 years ago

    Hi Irwin ,

     

    Try creating a calculated column like this:

     

    IF('Table'[Column]=BLANK(),0,
    IF('Table'[Column]<101,1,
    IF('Table'[Column]>100,"2",
    BLANK())))
     
    Jori
     
    If I answered your question, please mark it as a solution to help other members find it more quickly.

    Connect on Linkedin

     

7 Replies

  • jppv20's avatar
    jppv20
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Irwin ,

     

    Try creating a calculated column like this:

     

    IF('Table'[Column]=BLANK(),0,
    IF('Table'[Column]<101,1,
    IF('Table'[Column]>100,"2",
    BLANK())))
     
    Jori
     
    If I answered your question, please mark it as a solution to help other members find it more quickly.

    Connect on Linkedin

     

    • Irwin's avatar
      Irwin
      Icon for Helper IV rankHelper IV
      4 years ago

      Hi Jori,

       

      Thank you, this works.

      However, there is an issue. PBI treats all my 0 values as blanks. That means that if the value is 0 the calculated column will write it as a 0 aswell. From the above measure it should be a 1. Why is this? What can I do to circumvent this?

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Irwin 

     

    You can try below code:-

    new_column =
VAR result =
    IF (
        'Table'[Column] = BLANK (),
        0,
        IF ( 'Table'[Column] < 101, 1, IF ( 'Table'[Column] > 100, 2 ) )
    )
RETURN
    IF ( result = BLANK (), 0, result )

     

    Thanks,

    Samarth

    • Irwin's avatar
      Irwin
      Icon for Helper IV rankHelper IV
      4 years ago

      Hi,

       

      Thanks for the answer. I will definitely try this aswell. 👍

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Irwin  Nested IF is difficult to manage, try SWITCH

    Column = SWITCH(TRUE(), <condition1>, <Result1>,
                        <condition2>, <Result2>,<Result3>)
    • Irwin's avatar
      Irwin
      Icon for Helper IV rankHelper IV
      4 years ago

      Hi thanks for your reply. Unfortunately I couldt get a switch to work. It said something like it didnt understand "ranges". So it would work for blank values, but not for everything in between 0-100 or 100<