division by zero

[KamilaOlek]

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

[ankitpatira · Accepted solution]

@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.

[trees01]

You can now right click column in query and "Replace errors"... for anyone looking for a solution 10.2022

[Saitoh]

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.pdf

http://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

[Saitoh]

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.pdf

http://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

[Greg_Deckler · Accepted solution]

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.

[DuaneDLB]

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

[Anonymous]

@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."

[ankitpatira · Accepted solution]

@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.

[KamilaOlek]

As easy as that! Thanks a lot.!

[KamilaOlek]

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

[Greg_Deckler]

See your other thread on this topic:

http://community.powerbi.com/t5/Desktop/division-by-zero/m-p/48702