Forum Discussion
Solved
Refresh has stopped working due to 'divide by zero' error that should not be occuring
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 becaus...
- 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!
PavanLalwani
Resolver II
Resolver II- 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!
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!