Intermittent Microsoft SQL: Divide by zero error encountered

[Neale]

Hi, I have a report that always refreshes in the desktop Power BI app, usually works in the service but occaisonally fails due to divide by zero error. The error always occurs on the same table but might work 5 minutes later, during which time no data has changed.

I've tried extending the timeout, just in case, but that makes no difference.

Has anyone else come across the same issue?

Thanks 

[Poojara_D12 · Accepted solution]

Hi @Neale 

The divide-by-zero error happens due to inconsistent data during refresh. Fix it by:

• Replace division with DIVIDE(numerator, denominator, 0) to handle zeros.
• Ensure the source data is stable and consistent during refresh.
• Look for differences in query behavior.
• Use Power BI Service logs to find specific issues.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[ibarrau · Accepted solution]

Hi. Are you dividing some columns at some point? it might have an issue getting the data for the division and that's why it's zero. You can change the code to handle it. If your division is in power query you can check: https://learn.microsoft.com/en-us/power-query/error-handling

If it's from SQL you could use case when to check something first.

If you control de values of the division you could take an action to show you when it fails and whats going on.

I hope that helps,

[Neale]

Thank you. One of my divides is based on a SUM of a column that does includes NULLS. My understanding is that SUM will exclude NULL values but I don't know if this is faultless.

[Poojara_D12 · Accepted solution]

Hi @Neale 

The divide-by-zero error happens due to inconsistent data during refresh. Fix it by:

• Replace division with DIVIDE(numerator, denominator, 0) to handle zeros.
• Ensure the source data is stable and consistent during refresh.
• Look for differences in query behavior.
• Use Power BI Service logs to find specific issues.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[Neale]

Thanks for the tips. I'll work through my SQL, which is moderately complex with multiple nested select statements.

My point is that the SQL never fails in Azure Data Studio or Power BI Desktop. It only fails some of the time, approx 50%, on an automatic refresh in the Power BI Service. My dataset doesn't change particularly quickly so I'm reasonably confident that the report will fail and then succeed with the same data.