visual has exceeded available resources

[jgarciabu]

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:

Resources Exceeded

This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.
Please try again later or contact support. If you contact support, please provide these details.

Activity IDc7b563f3-c93d-2aa1-2784-082b8d81f234

Request IDe4c3c75a-060f-6b40-8ab0-7ed194ba10c3

Correlation ID69832fcb-c121-4ed4-ca06-89d5275e48e2

TimeThu Jan 19 2017 11:09:24 GMT-0500 (EST)

Version13.0.1700.1003

Cluster URIhttps://wabi-us-north-central-redirect.analysis.windows.net

[luismendoza80]

Found the solution on this page:

https://blog.crossjoin.co.uk/2023/06/18/the-visual-has-exceeded-the-available-resources-error-in-pow...

[cpwebb]

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

[Jeanxyz]

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?

[cpwebb]

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-d...

[PMY1965]

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.

[Jeanxyz]

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. 

[foodd]

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/PowerAppsIdeas

If 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.

[seerpo]

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. 

[hourir2]

** Potentially may help **
Hi all,

Had this error today in a MATRIX visual. I was pulling a column (Transit Stage) into the "Values" part of the matrix. Tried a simple filter on "Transit Stage" and got the "Resources exceeded" error.

I then realised i was filtering on the First [Transit Stage] value (see circled filter below), which was causing the issue, instead of filtering on the actual column itself.

Solution: I then pulled in the actual transit stage column itself into the visual filter, filtered on that, and it worked a charm.

[prettel1]

I had this issue and my personal proble was that the desktop where I was running the gateway was out of memory on the hard drive, I cleaned it and start the service again and then everything worked.

[DhananjayWalunj]

Hi All,

Is there an option to show the icon on the page and it will be visible only when the report shows the error "visual has exceeded available resources" and after clicking that icon we will jump to the default view where the icon is invisible?

[akkitek]

I'm having the same error for one of the table visuals but the same visual works fine when I publish it to "My workspace". Is there a difference in how personal workspace capacity and Azure capacities are configured? 

[pwrbiadm]

Can someone please provide the soultion to the issue "exceeded the available resources"?

I am unable to understand what is wrong? I don't have large DAX calculations. I have only added 4-6 more columns in one or two tables. My other visuals on the tabs on desktop has larger queries and measures. They all are running and refreshing just fine. I am having issue with just one visual. It doesn't make sense why with just one visual? 

[Jeanxyz]

Does the measure involves data from two fact tables? In most cases, DAX don't need to create a physical table to run the calculation, but when the data structure is complex, DAX has to create a physical table which can exceed the calculation capacity.

[Anonymous]

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. 

[Anonymous]

Hi,

Any  updates on this topic yet?

I'm having the strange thing that when I use publish to the web for my report I get the screenshot below as a result.
Only 1 out of 2 visuals is showing and the other one gives the exceeded memory error. Strangely both visuals (a table and a graph) use exactly the same data and filters (page level filters).

I had 2 years of logfiles and already filtered out 1 year with a report level filter but still keep having this issue.
The biggest DAX that I have is my own calendar table. Furthermore I have quite some Power Query steps.

Any suggestions on what to do?

[bdmichael09]

@Anonymous 

I managed to get around my issue without really intending to. I was looking to improve the performance of my report and rewrote all of my DAX measures using variables. Even a measure as simple as just summing some column was written using a variable and returning the output. The performance of the dashboard was drastically improved and it resolved my issue with the exceeded memory error. SQLBI has a Date table publicly available that you can borrow if you have trouble rewriting your DAX for the table. It's actually a great date table. It has pretty much everything you could possibly want in a date table. Hope this helps!

[cittakaro]

hi @bdmichael09 , can you provide a DAX example?

Thanks.

[bdmichael09]

@cittakaro Sure. None of my DAX is too crazy and is probably pretty basic to someone who is an expert. There might be better ways to do most of what I did but it works and loads quickly so I got what I needed. 

This is probably one of the more complicated measures I had in my report. I used to have everything here that you see as variables written into what is now the return statement. Sorry if it seems a bit like a jumbled mess. It's essentially just a series of nested if statements. After I started using variables, the performance of the report loading visuals was almost instantaneous rather than taking upwards of 10-15 seconds just to load a matrix/table visualization, if it loaded those visuals at all. 

EarnedTitleLevel# = 
VAR SurveylessthanTitle = [SurveyScoreGoal]<=values(TMD[Title Sort])
var SClessthanTitle = [StatusCountGoal]<=values(TMD[Title Sort])
var TTlessthanTitle = [TurnTimeGoal]<=values(TMD[Title Sort])
var AnswerlessthanTitle = [AnswerRateGoal]<=values(TMD[Title Sort])
VAR SurveylessthanSC = [SurveyScoreGoal]<=[StatusCountGoal]
var SClessthanSurvey = [StatusCountGoal]<=[SurveyScoreGoal]
VAR SurveylessthanTT = [SurveyScoreGoal]<=[TurnTimeGoal]
var TTlessthanSurvey = [TurnTimeGoal]<=[SurveyScoreGoal]
var SurveylessthanAnswer = [SurveyScoreGoal]<=[AnswerRateGoal]
var AnswerlessthanSurvey = [AnswerRateGoal]<=[SurveyScoreGoal]
var TTGoal = [TurnTimeGoal]
var SurveyScore = [SurveyScoreGoal]
var MinSurveys = [SurveyReceivedMin]
var MinCR = [CRCountMin]
var AnswerGoal = [AnswerRateGoal]
var statusgoal = [StatusCountGoal]
var CurrentTitle = values(TMD[Title Sort])
return

if(values(TMD[Specialty])="Mainstream PS" || values(TMD[Specialty])="NY PS",
    if(isblank(TTGoal) || isblank(SurveyScore), CurrentTitle,
    if(MinCR = -1 || MinSurveys = -1,
        if(SurveylessthanTitle && SurveylessthanTT,SurveyScore,
        if(TTlessthanTitle && TTlessthanSurvey,TTGoal,CurrentTitle)),
    if(SurveylessthanTT,SurveyScore,TTGoal))),
if(values(TMD[Specialty])="Hunt PS",
    if(isblank(statusgoal) || isblank(SurveyScore), CurrentTitle,
    if(MinSurveys = -1 || [RONAgoal]=-1,
        if(SurveylessthanTitle && SurveylessthanSC,SurveyScore,
        if(SClessthanTitle && SClessthanSurvey,statusgoal,CurrentTitle)),
    if(SurveylessthanSC,SurveyScore,statusgoal))),
if(values(TMD[Specialty])="CCS" || values(TMD[Specialty])="Escalation CCS",
    if(isblank(statusgoal) || isblank(SurveyScore), CurrentTitle,
    if(MinSurveys = -1,
        if(SurveylessthanTitle && SurveylessthanSC,SurveyScore,
        if(SClessthanTitle && SClessthanSurvey,statusgoal,CurrentTitle)),
    if(SurveylessthanSC,SurveyScore,statusgoal))),
if(isblank(AnswerGoal) || isblank(SurveyScore), CurrentTitle,
    if(MinSurveys = -1,
        if(SurveylessthanTitle && SurveylessthanAnswer,SurveyScore,
        if(AnswerlessthanTitle && AnswerlessthanSurvey,AnswerGoal,CurrentTitle)),
    if(SurveylessthanAnswer,SurveyScore,AnswerGoal))))))

A slightly simpler example is below here. Just for a bit of context that would make the code make a bit of sense without seeing the report, on each page of this report I've added in drop down slicers that the end user can use as selectors to adjust their goals for different tiers. Each of those slicers is reading off of a simple table that just has values that range from 0-100.

TurnTimeGoal = 
var CRCount = [CR Count]
var TT = [TurnTime]
var TCGoal = selectedvalue(TurnTimeTC[Column1])
var PCGoal = selectedvalue(TurnTimePC[Column1])
var ExecGoal = selectedvalue(TurnTimeExec[Column1])
var SrGoal = selectedvalue(TurnTimeSenior[Column1])
return

if(isblank(CRCount),blank(),
if(values(TMD[Specialty])="Mainstream PS" || values(TMD[Specialty])="NY PS",
    if(TT<TCGoal,4,
    if(TT<PCGoal,3,
    if(TT<ExecGoal,2,
    if(TT<SrGoal,1,0)))),blank()))

[Anonymous]

Follow up:

Is there any update, I am sitting at client's heat and don't have any words at the visual exceed limit error at power BI service reports.

Can anybody help me with this?