Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Irwin
Helper IV
Helper IV

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

 

1 ACCEPTED SOLUTION
jppv20
Solution Sage
Solution Sage

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

 

View solution in original post

7 REPLIES 7
Samarth_18
Community Champion
Community Champion

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Hi,

 

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

smpa01
Super User
Super User

@Irwin  Nested IF is difficult to manage, try SWITCH

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

 
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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
Solution Sage
Solution Sage

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

 

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 () )
)
)

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?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.