how to fix visual has exceeded the available resource.

Question

More details: Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either simplify the query or its calculations, or if using Power BI Premium, you may reach out to your capacity administrator to see if they can increase the per-query memory limit. More details: consumed memory 1331 MB, memory limit 1331 MB. See https://go.microsoft.com/fwlink/?linkid=2159752 to learn more.

Activity ID: 74e95a54-27fe-4300-8ad1-8f984503ac23

Correlation ID: 838a86ce-5ec3-b721-0cf5-4a7f96fb295c

Request ID: a23360e5-2162-439a-aa97-e5c9107fd9c6

Time: Fri Nov 03 2023 11:21:43 GMT-0700 (Pacific Daylight Time)

Service version: 13.0.21891.85

Client version: 2310.4.16370-train

Cluster URI: https://wabi-west-us-redirect.analysis.windows.net/

Answer 1

Hi Merlin,

The error message you're encountering in Power BI indicates that your query or the calculations it involves are consuming more memory than the configured limit. This issue can be resolved by optimizing your report, query, or dataset to reduce memory consumption. Here are some steps you can take to address this problem:

1. Simplify your query: Start by reviewing your query and its transformations to see if you can simplify them. Remove unnecessary columns, filters, or transformations that might be consuming excessive memory. Make sure you're only loading the data you actually need for your report.

2. Reduce the data volume: If your dataset is too large, consider limiting the amount of data you're loading into Power BI. You can filter your data to load only a subset of the data, such as a specific date range or relevant categories.

3. Use data summarization: Consider aggregating or summarizing your data at the data source level, such as in the SQL query or in Power Query, before importing it into Power BI. This can significantly reduce the amount of data that needs to be loaded into memory.

4. Optimize data model: In Power BI, use best practices for modeling your data. Avoid creating overly complex relationships, use role-playing dimensions when appropriate, and use calculated tables instead of calculated columns when possible.

5. Use DirectQuery or Live Connection: If your dataset is very large and you're using Power BI Premium or Power BI Premium Per User, consider using DirectQuery or Live Connection to connect to your data source instead of importing the data. This way, data is queried directly from the source, reducing memory usage in Power BI.

6. Increase memory limit (if using Power BI Premium): If you're using Power BI Premium, you can request your capacity administrator to increase the per-query memory limit. This can be a short-term solution if your dataset is close to the memory limit and optimization options are limited.

7. Monitor and profile performance: Use the Power BI Performance Analyzer and Query Diagnostics to identify which specific parts of your report or query are consuming the most memory. This can help you pinpoint areas that need optimization.

8. Consider using a more powerful machine: If you're using Power BI Desktop, make sure you're using a machine with sufficient RAM and processing power. Upgrading your hardware can help handle larger datasets more effectively during the development phase.

9. Review custom visuals and DAX measures: If you have custom visuals or complex DAX measures in your report, review them for performance bottlenecks. Some visuals and calculations can be optimized to use fewer resources.

10. Keep Power BI up to date: Ensure you're using the latest version of Power BI Desktop, as newer versions may have performance improvements and bug fixes.

By following these steps and optimizing your report and dataset, you should be able to reduce memory consumption and resolve the "exceeded available resource" error in Power BI.

Thank you!

Answer 2

@Merlin - Thanks for the question and using MS Q&A platfrom.

MS Fabric is currently not supported in the Q&A forums, the supported products are listed over here Supported products on MS Q&A (more to be added later on).

You can ask the experts in the dedicated MS Fabric community forum.

Can you please open as a new thread in MS Fabric community:  https://community.fabric.microsoft.com/t5/Forums/ct-p/PBI_Comm_Forums

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.