[Update 18th June 2023: this post about this error in the Power BI Service. If you’re running into this error in Power BI Desktop read this post]
Very occasionally you may find that a report that is slow to render – but still renders successfully – in Power BI Desktop, shows the error “Visual has exceeded the available resources” for some visuals when published to the Power BI Service:
Clicking on “See details” will show a dialog that looks something like this:
This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.
The official Power BI documentation has similar advice to what’s shown in this dialog about what to do here, but what’s really going on?
The information in the “More details” section of the section dialog gives you a clue: in this case it’s resource governance. When you run a DAX query in Power BI it will always use a certain amount of memory; inefficient DAX calculations can cause a query to try to grab a lot of memory. In Power BI Desktop these queries may run successfully but be slow, but the Power BI Service can’t just let a query use as many resources as it wants (if it did, it may affect the performance of other queries being run by other users) so there is a resource governor that will kill queries that are too resource hungry. In the case of the visual above the query behind it tried to use more than 1GB of memory and was killed by the resource governor.
The limits enforced by the resource governor cannot be changed in Power BI shared capacity. The limits are higher in a Premium capacity and vary depending on the capacity size, and if you are a Premium Capacity Admin there are a number of different settings on a capacity you can change that will affect this, described here. For example the Query Memory Limit (%) setting controls the amount of memory that any given query can use before the resource governor kicks in.
To demonstrate this I moved the workspace containing the report in the screenshot above to a Power BI Embedded A4 capacity and then changed the Query Memory Limit % setting to 1.
This resulted in the error dialog showing that the resource governor killed the query when it tried to use 1% of the 25GB of RAM available in an A4, which is 262144KB:
While it may be useful to change this setting if you’re the admin of an overloaded Premium capacity, the real solution is to tune the DAX calculations used in this visual (I suggest you read the section on materialisation starting on p568 of “The Definitive Guide to DAX” or this older article which covers the same ground) so they use less memory and return faster or change what the visual displays.
You may see the “Visual has exceeded the available resources” error in other scenarios where the resource governor is not involved. For example, setting the Query Timeout setting on the capacity to 5 seconds like so:
…means that the visual now times out after five seconds instead, and the “See details” dialog shows the following error instead:
More details The XML for Analysis request timed out before it was completed. Timeout value: 5 sec.
Good to know how these AAS properties are exposed for Premium datasets, thanks Chris!
Great article, please do dive more into this subject!
Have you come across any metrics that will show how many gets the error messages shown above?
As an admin it would be very informative to see how many are affected when tuning these limits.
No, I don’t think we can get any metrics for that but I agree it would be a useful thing to have. I’ll talk to the person who owns usage metrics about this.
Thanks mate,
I have raised the issue with Microsoft a few times, but they don’t seem to get the point. The crux of it all is that we, as admins, simply can’t do trial and error to see if the correct query memory limit is 0.3 or 0.5 on a live capacity.
Preferably we need some metrics to approximate how many will be affected when setting a new limit, and if not possible, then at least be able to see how many get the error messages after we have set it.
Otherwise, we are flying blind leaving the users and developers with possibly a very bad experience when trying to tune the limits.
Understood. BTW your articles on LinkedIn are excellent and widely read inside the Power BI team.
During development of a new dashboard we ran into this issue as well. Although we weren’t exactly sure what it was (hadn’t found your blog post yet), we suspected a memory/cpu limitation in Power BI itself. So we rebuild the data model in Analysis Services and connected to Power BI through DirectQuery.
Given your explanation above, I still think this was a reasonable solution, but it didn’t work. Do you have any idea why not?
Even i am facing the same issue but didnt get solution how to over come this
So, do you recommend to increase the timeout incase we face the second error? Is it more to do with direct query or import mode Data model? We are dealing with 50 million records in power bi import mode and one of the table is connected as direct query due to Data load limitations (trying to get that to import too)
No, I think before you increase the timeout you need to do some performance tuning. If you’re hitting a timeout you definitely have a performance problem.
Thanks Chris for your comment. Just new to the project, so will look into the details for opportunity of performance tuning though I have heard that they have barely used any DAX calculations etc. Need to check other areas as well.
Chris,
For Analyze in Excel, the query limit seems not to work as expected. Any thoughts about that?