cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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

2 ACCEPTED SOLUTIONS
Community Champion

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

Super User

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
10 REPLIES 10
New Member

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

New Member

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

New Member

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

Super User

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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
Not applicable

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

Community Champion

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

New Member

As easy as that! Thanks a lot.!

New Member

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

Super User

See your other thread on this topic:

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...