Error: Resource Governing

[DMSJennifer]

Hello,

I'm trying to load some data into my visual, but when I want to view a lot of data, my visual shows the following error:

• More information: Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either reach out to your Analysis Services server administrator to increase the per-query memory limit or optimize the query so it consumes less memory. More details: consumed memory 1024 MB, memory limit 1024 MB.
• Activity-id: ********-****-****-****-9a1a7576c768
• Correlation-id: ********-****-****-****-d3241b4c4530
• Request-id: ********-****-****-****-6641a53adb33
• Time: Tue Nov 30 2021 17:02:48 GMT+0100 (Midden-Europese standaardtijd)
• Serviceversion: 13.0.17186.43

I don't have any measures in this table, and if I use filters, I get the same message. 

Does anyone has an idea on how to solve this problem or how I can find the out what the cause of this problem is?

Thanks in advance!

Jennifer

[jasiluka12]

i get the same error and it makes no sense whatsover. My matrix has around 8 measures, but in DAX Studio the query of the whole matrix returns results in 2-3 seconds. How it can hit the memory limit is beyond my imagination. This makes powerbi completely useless when you want to have a visual with 5+ measures as it can crash anytime. 

[cpwebb]

The amount of time it takes the query to run is irrelevant. The problem is that there is something - probably to do with the way you have written the DAX for your measures - that is causing a huge memory spike when the query for the matrix is run. It can be something like adding a zero to all your measures:
https://blog.crossjoin.co.uk/2024/07/07/dax-measures-that-never-return-blank/
or using FILTER with CALCULATE:
https://blog.crossjoin.co.uk/2024/06/30/calculate-filter-and-dax-memory-usage/
or having a table or matrix with a scrollbar:
https://blog.crossjoin.co.uk/2024/07/14/why-power-bi-table-visuals-with-scrollbars-can-cause-problem...

Can you post the DAX for your measures?

[jasiluka12]

yes i managed to discover the issue that allows to load the visual... at least for now ...

The problem was that in some of my measures i had a variable as :

VAR _AddNulls =  IF ( ISINSCOPE ( Dim_Accounts[KEY ACCOUNT] ) = FALSE, 0, BLANK () )

 and then at the bottom i had a SWITCH statement based on KEY ACCOUNT with some of them having + _AddNulls at the end. 

removing this _AddNulls variable helped to load the visual again.. although its not ideal as business requirement was to display 0 instead of blanks. 

I get that the syntax is not perfect and numerous switch + isinscope statements lead to performance degradation.. however I cant think of a better way to display Profit and Loss table for example.

Precalculating it in a table or external paid visuals are not the suitable options in my case so i had to rely on Matrix visual + DAX. 

[cpwebb]

So this looks like the scenario I wrote about in one of the blog posts above - you have a measure that never returns a blank. This can be a cause of memory spikes.

[jasiluka12]

exactly. Just in my case i never wanted to return blank at KEY ACCOUNT level. Although the Matrix had just <100 rows at maximum with 8 measures. And Fact table from where all the measures were calculating results from is <100k. So in theory the there were not many data points in this visual , even if its expanded to the lowest level ( i had 3 fields in the ROWS section of the matrix). 

Also one thing i noticed was that i got this error only when drilling down on a specific category from Level 1 to Level 2 to KEY ACCOUNT (3rd level).
However once you drill down via buttons on the top right of the matrix visual and expand all rows to the lowest level, the visual loaded just fine. 

So something really weird going on with the way PowerBI handles it.

I would expect that expansion of single row takes fewer memory than expanding all categories.

[V-lianl-msft · Accepted solution]

Hi @DMSJennifer ,

Depending on the data source and the number of fields (measures or columns), a visual may load slowly. We recommend limiting visuals to 10-20 total fields, both for readability and performance reasons.

The upper limit for visuals is 100 fields (measures or columns). If your visual fails to load, reduce the number of fields.

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-data-points 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vishal097]

Hello @V-lianl-msft 

Does calculated coloum also can cause this error ? 

[amitchandak]

@DMSJennifer , if you are a premium customer you can use you can install capacity app

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-premium-monitor-capacity

or you can use SQL server profile to monitor the queries

refer

https://blog.crossjoin.co.uk/2020/03/02/connecting-sql-server-profiler-to-power-bi-premium/

[Power-BiGuru]

Hi, 

Will this issue be solved if we buy premium only for one user(admin) and pro for other users in my organization?

[DMSJennifer]

And what are the options if you're a pro user? Is the only solution a premium account or are there some workarounds to solve this problem?