Forum Discussion
division by zero
Hello,
I have encountered problem, that is killing me. I need to use Direct Query mode, which does not support IFERROR function.
I have several measures, that sometimes return error.
For example I have two columns:
NetValue
QuantityKG
QuantityKG is sometimes 0.
I need to make a measure: NetValue / QuantityKG. If it is an error I can have 0 calculated.
The measure would not be visible in a visual, because of division by zero, which I cannot easily solve with IFERROR statement.
I tried filtering values but it wouldn't help.
Do you have any idea how can I overcome this issue?
Thanks
KamilaOlek you can turn on feture to allow unrestricted measures in directquery which should then be able to use IFERROR function. You can go to File -> Options and settings -> DirectQuery.
What about just a simple IF statement?
=IF([QuantityKG] = 0,0,DIVIDE([NetValue],[QuantityKG])
You can also use DIVIDE function, probably a little cleaner:
=DIVIDE([NetValue],[QuantityKG],0)
DIVIDE will return the 3rd parameter as the value if a divide by zero occurs.
10 Replies
What about just a simple IF statement?
=IF([QuantityKG] = 0,0,DIVIDE([NetValue],[QuantityKG])
You can also use DIVIDE function, probably a little cleaner:
=DIVIDE([NetValue],[QuantityKG],0)
DIVIDE will return the 3rd parameter as the value if a divide by zero occurs.
Greg_Deckler Can I do this for a custom column ?
I tried it and got an error "Expression.Error: The name 'DIVIDE' wasn't recognized. Make sure it's spelled correctly."I like your suggestion how to handle division by zero. I created this measure but need to change it to only calculate when a specific value is met.
% Driving Hrs = DIVIDE(sum(DriverLogs[driving_hrs]), sum(DriverLogs[eleven_hr_rule]) + sum(DriverLogs[driving_hrs]),0)I need to figure out how to create this measure to only calculate when DriversLogs[Off Duty Hrs] < 24. suggestion? Thanks
KamilaOlek you can turn on feture to allow unrestricted measures in directquery which should then be able to use IFERROR function. You can go to File -> Options and settings -> DirectQuery.
As easy as that! Thanks a lot.!
Hello,
I have encountered problem, that is killing me. I need to use Direct Query mode, which does not support IFERROR function.
I have several measures, that sometimes return error.
For example I have two columns:
NetValue
QuantityKG
QuantityKG is sometimes 0.
I need to make a measure: NetValue / QuantityKG. If it is an error I can have 0 calculated.
The measure would not be visible in a visual, because of division by zero, which I cannot easily solve with IFERROR statement.
I tried filtering values but it wouldn't help.
Do you have any idea how can I overcome this issue?
Thanks
See your other thread on this topic:
http://community.powerbi.com/t5/Desktop/division-by-zero/m-p/48702
The division by zero is uniquely and reasonably determined as 1/0=0/0=z/0=0 in the natural extensions of fractions. We have to change our basic ideas for our space and world:
http://www.scirp.org/journal/alamt http://dx.doi.org/10.4236/alamt.2016.62007
http://www.ijapm.org/show-63-504-1.html
http://www.diogenes.bg/ijam/contents/2014-27-2/9/9.pdfhttp://okmr.yamatoblog.net/division%20by%20zero/announcement%20326-%20the%20divi
Announcement 326: The division by zero z/0=0z/0=0 - its impact to human beings through education and research
The division by zero is uniquely and reasonably determined as 1/0=0/0=z/0=0 in the natural extensions of fractions. We have to change our basic ideas for our space and world:
http://www.scirp.org/journal/alamt http://dx.doi.org/10.4236/alamt.2016.62007
http://www.ijapm.org/show-63-504-1.html
http://www.diogenes.bg/ijam/contents/2014-27-2/9/9.pdfhttp://okmr.yamatoblog.net/division%20by%20zero/announcement%20326-%20the%20divi
Announcement 326: The division by zero z/0=0z/0=0 - its impact to human beings through education and research
You can now right click column in query and "Replace errors"... for anyone looking for a solution 10.2022
