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.
Completely agree AndrewD – Power BI is not fit for purpose – Qlik is a far superior tool.
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.
Hey Chris, I am having the same issue. I am using only the actual data with no DAX or transformation in PBI. I’ve two tables with a relationship and when I try to use the column I want in a table visual I get the Resource limit exceeded error (Limit is 10240 mb) and on selecting columns form the second table (after making selection in 1st table) this happens. The req is to showcase all that data without any aggregation or transformation. This is being done via PBI Service. Is there any other solution we can try to overcome the resource limit error and prevent timeout error too (which is coming at times for the same thing).
Hi Chris, can this issue happen in Premium Capacity models? We had this issue and all the visuals on the report had same error. Before these errors, the recalc operation on the PBI Model has failed. does that mean the memory error is due to the recalc operation failure or query inefficiencies?
Yes, it can happen on Premium capacity. If the recalc failed that could be the cause of the problem or the recalc failing and this error could be symptoms of the same problem. It’s hard to say.
I have a table visualization with 5 rows of sales data (8 columns total). It has a column called Invoice Date. I would like it to ONLY display data for the latest invoice date. The visualization displays all whopping five rows of data; however, if I change the Invoice Date column from “Don’t summarize” to “Latest,” it bombs out and tells me the visual exceeds resources. It makes NO SENSE. It should be removing ONE row from the five rows of data shown. HOW could that “exceed resources?”
Well your original visual could be just below the limit and adding the Latest aggregation could add a really small additional load that pushes you over the limit…?
Hello, I’ve had a report up and running for three years, and a few weeks ago I began getting this message on a few visualizations. At first, I assumed my data had grown and surpassed a threshold. but even filtered data won’t generate the visualizations, with much less data than before. Have there been any changes to Power BI service that may cause this behavior?
This is a really strange topic. I have a matrix with around 8 measures. Some of them have several switch true statements. However via performance analyzer matrix runs in 4seconds.
My settings are set to timeout after 255 seconds, however after adding few more switch statements to one of the measure, i started getting this error. Even though the matrix still runs in <5 seconds in dax studio. So the whole matrix works quite fast.
I then set my powerbi limits to "no limits" option and the visual started to load again and without any additional delay. In fact it works quite fast.
So i dont really understand this "exceeding resources" issue at all. If my visual takes <5 seconds and timeout is set to 255 seconds, why it would exceed resources… ?
How long your query runs for is irrelevant. What matters is how much memory it needs to use: it sounds like you have written a measure that consumes a lot of memory very quickly. Using Switch and True is probably the cause: it’s one of the ways the engine can end up doing some evaluation for every branch of the Switch, even the ones that aren’t used. Check out https://blog.crossjoin.co.uk/2022/09/19/diagnosing-switch-related-performance-problems-in-power-bi-dax-using-evaluateandlog/amp/ for ways to diagnose your problem.
I just had this happen and it was because a relationship had been automatically deleted in the model view. Not due to a lack of resource. Seems like they’re not always showing the correct error message.