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

[Irwin]

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 ? 🙂

[Samarth_18]

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]

Hi,

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

[smpa01]

@Irwin  Nested IF is difficult to manage, try SWITCH

Column = SWITCH(TRUE(), <condition1>, <Result1>,
                        <condition2>, <Result2>,<Result3>)

 

[Irwin]

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<

[jppv20 · Accepted solution]

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]

Hi again...
I tried rewriting it since I read that PBI thinks =Blank() also includes 0. See this post: 

https://community.powerbi.com/t5/Desktop/DAX-treats-0-as-BLANK-how-to-prevent-it/m-p/342272

When I use the below it works. Thank you again.

Measure =

IF (

ISBLANK('Table'[column]),0,

IF (

'Table'[column] < 1,

1,

IF ( 'Table'[column] >= 1, 2, BLANK () )

)

)

[Irwin]

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?