One of my most popular blog posts of the last few years is this one on the “Visual has exceeded available resources” error in the Power BI Service:
https://blog.crossjoin.co.uk/2020/01/20/visual-has-exceeded-the-available-resources-error-power-bi/
This error only used to appear in the Power BI Service, but the good news is – and trust me, this is good news – it may now appear in Power BI Desktop too following the May 2023 release.
First, a quick recap of what causes this error. The Power BI Service has finite resources so we at Microsoft don’t allow you to run queries there that take hours to complete or consume vast amounts of memory; we impose limits on query duration and memory usage, and you’ll see this error in the Power BI Service when you hit these limits. The problem has been that, up until recently, these limits were not imposed in Power BI Desktop so it was easy to develop inefficient reports and datasets that worked ok (if a little slowly) on the developer’s PC but then caused errors after they were published. What has changed is that these limits are now imposed in Power BI and they are also configurable there.
The announcement of this change is here:
https://powerbi.microsoft.com/en-us/blog/power-bi-may-2023-feature-summary/#post-23312-_Toc134425717
How do you know if you are running into these limits? You’ll see an error in your visual in Power BI Desktop that looks like this:
The error message is:
Visual has exceeded the available resources
If you click on the “See details” link you’ll see the following dialog:
Resources Exceeded
This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.
What should you do if you encounter this error? The most important thing is to understand why it’s happening. There are two possible causes:
- Your query is taking too long to run. For the Power BI Service, the default query timeout is 225 seconds although it is possible for an admin to reduce this; unless you’ve set a custom limit or you’re not using a Power BI dataset as a source, it’s likely that 225 seconds is the longest that a query will run for in Power BI Desktop.
- Your query is using too much memory. This is probably because you are doing an inefficient DAX calculation on a very large table (filtering on entire table rather than a single column is a classic anti-pattern, for example).
As a result you need to do some tuning. “But Chris!”, you say, “my query has to run for longer than 225 seconds! It’s too difficult to tune!” To which I say – tough, you don’t have a choice. Slow, inefficient queries result in unhappy users so if you don’t fix these issues you have an even bigger problem. This video of Marco’s is a good place to start if you want to learn about performance tuning.
In order to do that tuning though (or if you just want to get stuff done before you do any tuning, or if you think the wrong limits are being imposed) you’ll need to turn off the limits so you can capture the DAX query for the offending visual using Performance Analyzer. To do this, go to File/Options and settings/Options to open the Options dialog, go to the Report settings pane and scroll to the bottom to see the Query limit simulations section:
You can either use the “Custom limits” option, as shown in the screenshot above, to set your own limits (enter 0 in one of these boxes for no limits to be imposed) or use the “No query limits” for no limits. You should only use these options temporarily, remember, otherwise you run the risk of getting this error in the Power BI Service later on!
It’s worth mentioning that the limits imposed in Power BI Desktop are only imposed on queries generated by Power BI Desktop itself, which means that they won’t affect external tools like DAX Studio that can also be used to query a dataset in Power BI Desktop. You can see how the limits are imposed by running a Profiler trace on Power BI Desktop, finding the Query Begin event for each query and looking for the Timeout and DbpropMsmdRequestMemoryLimit properties in the Property List shown under the query text:
Also, these settings are saved on a per-file basis, so if you create a new .pbix file it will have the default settings and not the settings you made in any other .pbix file.
Hi Chris, thank you for sharing this useful feature. What specific limit settings would realistically reflect the resources and behavior of a Pro, Premium per User, or PRemium workspace?
If you choose Pro, PPU or Premium in the Options dialog it will tell you what settings it uses.
thank you for the article
I ran into the Resources exceeded just by switching a column in a table from “Don’t Summarize” to “Summarize”. I figured that would give me LESS rows. The table works fine when not summarized, but exceeds resources when I try to summarize one of the columns. And the query only takes like 10 seconds to run. None of these limits ever make sense; if you read the forums, SO MANY PEOPLE are getting resources exceeded errors only really tiny tables and super fast queries.
Hi Chris, great article. I am running into this issue now in my report. I am using serverless Synapse and Power BI to query from my ADLS Gen2. I am visualizing a very small amount of data from my bronze layer, but the filtering is taking too much time. I am also using a star schema. I cannot import the data into PowerBI as the size is too much. The silver and bronze layer are directly imported into Power BI and performing fine. How would you suggest I proceed from here?
How much data do you have?
Great article. I am running into this issue even when i filter to a single row. I am trying to display data from two tables with one joint , no complex calculated field , a very simple display of 9 fields using table . I never had these types of issues with tableau or qlickview . This is so illogical and sadly the usual fix for msft products (alt-ctl-el) does not help here.