March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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 ? 🙂
Solved! Go to Solution.
Hi @Irwin ,
Try creating a calculated column like this:
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. 👍
@Irwin Nested IF is difficult to manage, try SWITCH
Column = SWITCH(TRUE(), <condition1>, <Result1>,
<condition2>, <Result2>,<Result3>)
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<
Hi @Irwin ,
Try creating a calculated column like this:
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.
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
83 | |
76 | |
62 | |
58 |
User | Count |
---|---|
141 | |
122 | |
105 | |
94 | |
90 |