Forum Discussion
visual has exceeded available resources
Hi All,
I've been using PowerBI for the better part of a year now. For the last month and a half or a little more, we've had annoying failures to refresh for 6 of our 14 tile visualizations. It's also inconsistent. Some days certain tiles of the 6 refresh and others they don't. The dashboard in question contains nothing more than 14 pinned single data point cards. They are simple queries made to our Azure SQL Database. On the database, all the queries run immediately with no delay. I have all my visualizations set to connect live to our Azure database. I'm at a loss for what to do. It seems like it's an issue with resources on Microsoft's servers. Here's the error I get:
Please try again later or contact support. If you contact support, please provide these details.
Hi jgarciabu,
Yes, the issue occurs when a visual has attempted to query too much data for the server to complete the result with the available resources.
As suggested in the error, you may need to try filtering the visual to reduce the amount of data in the result currently.:smileyhappy:
Regards
61 Replies
There are several topics where this issue is mentioned, but it looks like this one has the extensive discussion. I'd like to know what resources we're talking about. Is it CPU or memory? Is the limit on the server hosting SSAS, on the Gateway server, on the pc/laptop running Power BI Desktop/Service or is the limit on Microsofts side so that we cannot upgrade the resources that are being exceeded? If anyone knows anything about this, I would really appreciate it if you'd share it here.
Also having this issue. Does not make sense. Need to understand limits better as it is turning a supposedly enterprise-level tool into a bear for certain critical tasks within my relatively-small dataset.
I am having the same issues. The dataset is not huge but I do have a number of measures becasue I need the model to be dynamic. The model works on the desktop (slow but works), but in the serivce it sometimes works and sometimes crashes. Also, I have noticed that the "spinning wheel" that used to tell me if the system was still calculating, is now gone and so I have no idea when all the calculations are compleded (I assume it vanishes when a model is nearing the resource limit). It's quite frustrating becasue you start to think that the model is stuck and then all of a sudden the data in refreshes.
Is this all becasue we are not signed up to Premium Capacity? Other than major corporations, who can afford Premium Capacity at $4,000/month (or more)??? And then you start looking at Microsoft's pricing model for usage you start to wonder how anyone can really understand what their monthly bill is ever going to be. I call it the "Rube Goldberg Pricing Model". Very frustrating.
as I understand the reply from Microsoft - it is the size of the record set. Not the dax or visuals per se. So i.e. instead of trying to pull in X years of data - segment that / filter to a smaller data set.
but what is not said is how much is too much.... that would be worthy of note.....
There are definitely some limitations in regards to in-memory analysis as well. I imagine that Power BI is running Azure Analysis Services behind the scenes and that a certain amount is allocated to each user.
My tabular dataset isn't huge. I've seen this when I'm doing a significant number of date filters in a DAX measurement to create a date-dynamic measurement for historical trends. It takes a good chunk of my RAM locally when running, but completely borks when it's published.
I guess the next step would be to set up my own SSAS for my tabular model, rather than relying on Power BI's.
interesting to hear. in your comparison - how much RAM locally is it taking a good chunk of?? are you up in the 32G area on a server or more?
I was able to get around this issue by identifying which table caused the slow down. I turned off "Enable load" for that table. Then referenced the result of that table into a new table which I loaded into the report. By keeping the most complex calculations in the background query and only loading its results, my report went from taking 20 minutes to return an error to returning accurate results within 5 seconds.
Dear all
We were dealing with this issue a year and a half ago and it was an absolute pain. We had numerous conversations with the official Microsoft support at the time and, in all honesty, they couldnt help much except for advising us that the issue was our SQL Server (queriestaking more than 5 seconds...); limit the number of the visuals in a single report. In a nutshell, limit the amount of data to be fetched; make sure to put solid indexing.
So:
1. Check the SQL query performance. End-result in Power BI depends on the performance of your backend.
2. Further tune it with indexing.
3. Consider having an SSAS solution of your data instead of taping on schema tables directly.
good luck.
I got this error message today with my SWITCH. Power BI doesn't allow over 50 conditions in the function. Update: I found Chris Webb's solution. I double Query Limit in Report settings and it works.
This solution works on my report. I have adjusted the Query-Limit simulations to 10gb, and my visuals are now working without any error when I added more values to my matrix table.
Does changing the query limit works for PB Service or not? I understand from Chris Webb's blog, this tuning only work in Power BI Desktop, so won't you have the same issue again?
also I'm not sure if more than 50 conditions can cause the issue. In my case, my Power BI report works online at the beginning, but one year later, I start to get capacity error message. I didn't make any change with the measures during the period, so my problem can only be caused by data size.
Hello Jeanxyz as the Original Post was added in 2017, and marked as Solved long ago, It may be helpful to bring your question to the Community Forum in a fresh post.
Issues
If you have found a true bug or issue, you can make a post to the forum to let others know (and that would be very nice of you to do so). But if you want to get it fixed, you should post it in the Issues/Ideas (it varies) area of the community. Specifically, you should post it here:
Power BI - https://community.powerbi.com/t5/Issues/idb-p/Issues
PowerAutomate - https://powerusers.microsoft.com/t5/I-Found-A-Bug/bd-p/BugFound
PowerApps - https://ideas.powerapps.com
Ideas
If you have a new idea for functionality or how something should work, you can certainly start a thread on the community to solicit feedback on your idea, but you should also post the idea in the ideas section of the website and solicit others to vote on your idea. The development teams use the Ideas area of the communities to build their development pipeline and a significant amount of weight is placed on Ideas that have lots of votes versus those that do not. The ideas areas of the communities are here:
Power BI - https://ideas.powerbi.com
PowerAutomate - https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas
PowerApps - https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeasIf your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.
To be clear: you can indeed stop this problem happening in Power BI Desktop by changing the "Query limit simulations" settings (see this blog post for more details), but that only stops the problem happening in Power BI Desktop. You also need to care about what happens after you publish your report to the Power BI Service and it's not so easy to change settings there to avoid the error (see this older post of mine, a companion to the previous post I mentioned, too).
Looking at the other posts on this thread I have some general suggestions for people running into this problem:
- If you're using DirectQuery mode you probably shouldn't be. About 90% of the people I see who are using DirectQuery have made the wrong choice and should be using Import mode instead - it's almost always faster and easier to tune. If you do need to use DirectQuery this recording of a user group presentation I gave on DirectQuery best practices might be useful.
- If you're using Import mode with a relatively small amount of data - I see people here with only a few million rows of data - then it's almost certain that the problem is either the way you have modelled your data or something you are doing in the DAX for a measure. It's hard to give more specific advice because there are so many things that can go wrong, but for example the DAX antipattern of filtering on a whole table in CALCULATE can cause huge memory spikes which lead to errors even on fairly small models. There are a lot (too many?) resources out there on how to tune your reports but this is probably a good place to start.
Chris Webb
I tried everything you mentioned and brings down the model size from 90MB to 50MB, but the error message remains. After lots of checking, I realize that by remove a group measure, the memory need will reduce by 50%. To detailize, in the first table, I used a grouped measure, the maxtrix calculation takes 10174ms:
**********************
Project Margin = SWITCH(SELECTEDVALUE('Par_Project Margin_Calculation'[ID]),1,[Revenue],2,[Direct Costs],3,[Direct Labor - Project Margin],5,[Gross Margin - Project Margin2],6,if(ISBLANK([Gross Margin % - Project Margin2]),BLANK(),format([Gross Margin % - Project Margin2],"0.00%")),7,[Approved Days - Project Margin],11,[Avg Rev per working day - Project Margin])**********************************In the 2nd matrix table, I simply dragged the sub measures ([Revenue], [Direct Costs]....) directly into the value field, and this time the DAX calculation time is reduced significantly. It seems the VertiPaq engine works quite differently in the second case. But I still don't know exactly why the calculation memory need is so much different. Maybe the capacity limit is set per visual query, in the first table, there is only one query, in the second table, there are multiple query involved?Looking at your code, I've just noticed that your SWITCH checks the value of a numeric ID column on a table. I assume that there is either a slicer or filter somewhere where the user sees the name associated with each ID, or the names are displayed on the report? If so then you could be running into this issue with SWITCH: https://blog.crossjoin.co.uk/2022/09/19/diagnosing-switch-related-performance-problems-in-power-bi-dax-using-evaluateandlog/
Hi jgarciabu,
Yes, the issue occurs when a visual has attempted to query too much data for the server to complete the result with the available resources.
As suggested in the error, you may need to try filtering the visual to reduce the amount of data in the result currently.:smileyhappy:
Regards
I don't think this issue is solved. What are the actual limits, and are they the same for all visuals? I'm having this issue with a simple date filter which is querying a view that has only a few thousand rows. We barely have 1.5 years of data, this is pathetic.
Is this a limitation in the Power BI Web Service?
I'm also working with a fairly large DAX calculation - it works fine on my Desktop environment, but fails to load as soon as it's published.Why is this issue marked as solved? What is the "available resources" and what is the solution?
In my instance, some users are seeing this issue in the PowerBI service and some are not. How do I deal with this kind of issue? It would be terrible to believe a visualization is working properly, go out on a sales opportunity to a client and have this fail like this.
I am having the same issue!!
"visual has exceeded the available resources"
Can anybody help me out, I am at the on-site project and prepared 100+ reports.
Report having an issue of visual limit and no data is able to show but the report is not that much big only few measures are calculating.
Please, can anybody help me out?
Hi ,
I faced the same error in a table visual. I changed the datatype of a numeric column(having 16 digit values)
from whole number to decimal and republished the report. It fixed the issue.
Hi, I am too facing this issue. I have created my report using direct query in Azure SQL Database. My table has about 445 rows and a measure with sum of all values. Even though it is showing "Visual has exceeded avaliable resources".
Im also facing this issue...What are the exact limitations when it comes to Power BI Service?
