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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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