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.
Hello,
When divided by zero, it generates NaN and infinite value. Can you please help me with how to replace these values with zero when creating custom columns? The picture below, "Collection in %_June" is a custom column, the current formula is [Collections_June]/[Billings_June] which produces NaN and infinite values when column Billings_June contains zero.
Thanks so much!
Solved! Go to Solution.
@amitchandak Yes. please see the error message below when use "DIVIDE" in the custom column.
I just figured out this can be resolved by "if...then...else...". Here is the solution and it worked -
if [Billings_June]=0
then 0
else [Collections_June]/[Billings_June]
Thanks though!
Hi @lilySixteen ,
I assume you are showing these details of Power Query and it is showing NAN or Infinity as [Billings_June] is 0 in some rows.
SO when you create a Custom Column
Write this
if [Billings_June] <> 0 then [Collections_June]/[Billings_June] else [Collections_June]
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
@harshnathani Thanks! This has already been resolved before you post your answer. I think your answer would work correctly as well. Thanks though!
@lilySixteen , I can formula is created in power bi, then create like
divide([Collections_June],[Billings_June])
If created elsewhere. Use replace function in power query
https://yodalearning.com/tutorials/learn-how-replace-values-power-query/
You should able search using NaN
@amitchandak Can't use "divide" in the Custom column. The replace value option doesn't work well, after replace "NaN" with "0", there's still NaN values showing in the column.
@lilySixteen , I did not get can not use divide?
This happens because you are using DAX and the question is for M, in PowerQuery. I had the same problem and found this answer by coincidence, it doesn't really solve the problem.
@amitchandak Yes. please see the error message below when use "DIVIDE" in the custom column.
I just figured out this can be resolved by "if...then...else...". Here is the solution and it worked -
if [Billings_June]=0
then 0
else [Collections_June]/[Billings_June]
Thanks though!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |