Refresh has stopped working due to 'divide by zero' error that should not be occuring

[Oaabwen42]

I now receive the error

"Failed to save modifications to the server. Error returned: 'MdxScript(Model) (69, 67) A division by zero has been encountered. The current operation was cancelled because another operation in the transaction failed. '."

when trying to refresh a report. This didn't happen on previous refreshes even this morning, and nothing has been changed either in the report or in the data the report uses. I tried removing all the measures which use division to make it impossible for anything to divide by zero. Yet I still received this error, although sometimes with different numbers referenced (the "69, 67" in the pasted text above might change to "161, 67" or "41, 67").

I'm stumped. Any ideas on what's going on here?

[PavanLalwani · Accepted solution]

• This kind of intermittent “divide by zero” error during refresh, especially when nothing has changed in the data or report, can indeed be frustrating. Here are several steps that might help identify and resolve the root cause:
• Check Calculated Measures and Conditional Logic
  - Sometimes, if there are calculated measures that involve division, they might indirectly reference data that contains zero values in scenarios that didn’t occur before, leading to new errors.
  - Use `IF` or `DIVIDE` functions: Even if you believe you removed all division measures, double-check any calculated measures or columns to ensure all divisions are wrapped in safe error-handling logic. For instance:
  ```DAX
  DIVIDE([Numerator], [Denominator], 0)
  ```
  Here, `DIVIDE` is safer than using `[Numerator] / [Denominator]` directly, as it handles zero errors gracefully.
• Examine Query Dependencies and Data Sources
  - Sometimes, underlying data source changes (even minute ones like schema tweaks or metadata updates) can affect the behavior of Power BI models during refresh.
  - If you are using any DirectQuery sources or dynamic queries, examine the connections and confirm that they’re stable and intact.
  - Check if there’s any caching or latency issue from the source system that could momentarily cause an empty or unexpected dataset to be returned.
• Review Data for Null or Empty Values
  - Sometimes, even if the source data has not changed, it’s possible that null or unexpected empty values have been introduced or interpreted differently by Power BI during refresh.
  - Use Power Query Editor to check any transformations where nulls might slip through as zeros, especially in columns involved in division calculations.
• Inspect Power BI Model and Schema Errors
  - Go to Model View in Power BI and look at any relationships and columns involved in measures. Often, a schema issue or update might cause unexpected behavior.
  - Ensure that data types are consistent and properly set, as Power BI can misinterpret data types under certain conditions (like importing versus direct querying).
• Enable Step-by-Step Refresh and Isolate Errors
  - In Power BI Desktop, consider refreshing in small steps (if feasible). If your model is complex, do a partial refresh of tables that are more likely to contain division logic and see if you can isolate the issue to a specific measure or column.
  - You can also test refresh on smaller data samples or limit data to specific time frames to verify if this affects the error.
• Clear Cache and Refresh Environment
  - Sometimes Power BI Desktop retains cache that can cause unexpected behavior. Go to File > Options and settings > Options > Data Load and clear cache.
  - Also, if you have published the report to the Power BI service, try refreshing the published version to determine if the issue is specific to the desktop environment.
• Examine Power BI Service (if applicable)
  - If the error only occurs in Power BI Service, it could be related to service-side issues or configurations. Check if Power BI’s service logs or settings provide any clues.
• If none of these work, it might be helpful to look at the error details in the Power BI Desktop Trace Logs. These logs can sometimes reveal the exact measure or dependency causing the issue. You can enable tracing in Options > Diagnostics and then review the log file after reproducing the error.
• If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!

[Oaabwen42]

Tested all of these. Clearing the cache then refreshing each table individually got it to start working again! I've also updated the published version on the PBI service. Thank you so much!

[Idrissshatila]

Hello @Oaabwen42 ,

maybe this division is occuring in power query and is giving an error, can you try to check this.

also, if you didn't find the reason, I suggest you open an issue with microsoft support, it may be a bug.

https://community.fabric.microsoft.com/t5/Issues/ct-p/PBI_Comm_Issues

[Kedar_Pande]

@Oaabwen42 

Even if you removed measures with division, it’s helpful to wrap any division in DIVIDE (instead of /), which handles zero safely:

SafeDivision = DIVIDE(Numerator, Denominator, 0)

If null values are present, update measures to handle them by using COALESCE or IF(ISBLANK(...)).

Sometimes, cache or background processes might retain old errors. Save and restart Power BI Desktop to clear any cached data or residual errors.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[parry2k]

@Oaabwen42 not sure what these coordinates are, and I don't think you can see mdxscript. 

[parry2k]

@Oaabwen42 very hard to tell since you have removed all measures using division. Cannot think of anything else except checking the measure? Do you have any visual calculations?

[Oaabwen42]

No visual calculations. There are filters on visuals, but none of those use division in any form. Is it possible that something different is wrong and this is just the closest error message PBI could give to the problem?

Alternatively, is there a way to directly look at MdxScript(Model) (69, 67), assuming that 69 and 67 are coordinates?

[parry2k]

@Oaabwen42 interesting, does the service have the same error message?

[Oaabwen42]

Essentially, it says "MdxScript(Model) (159, 67) A division by zero has been encountered."

[parry2k]

@Oaabwen42 is this happening on the service or on the desktop? if it is on the service, can you try to refresh the desktop to see if it works or you might get a different error message.

[Oaabwen42]

The error message I shared is on desktop, although the error occurs on the service too