Skip to main content
cancel
Showing results for 
Search instead 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

Reply
KamilaOlek
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
ankitpatira
Community Champion
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.

 

 

Capture.PNG

View solution in original post

Greg_Deckler
Super User
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.


Follow on LinkedIn
@ 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...

View solution in original post

10 REPLIES 10
trees01
New Member

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

Saitoh
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://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
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://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
Super User
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.


Follow on LinkedIn
@ 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...

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

ankitpatira
Community Champion
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.

 

 

Capture.PNG

As easy as that! Thanks a lot.!

KamilaOlek
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

 

See your other thread on this topic:

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

 


Follow on LinkedIn
@ 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...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.