Forum Discussion
visual has exceeded available resources
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
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/
