Resources Exceeded - how to check how much memory my query consumes?

[Mariollo]

I am using a Pro license with a 1GB limit for my queries.

I have a table with 17 million rows where I need to calculate the standard error of the mean, standard deviation, etc., so each row is required.

I am struggling with a "Resources Exceeded" issue.

I moved the logic to PQ, but it didn’t work well.

How can I check how much memory my query consumes?

What other steps can I try to avoid this issue?

[Anonymous]

Hi,

Thanks for the solutions darkniqht  and PijushRoy  offered, and i want to offer some more information for user to refer to.

hello @Mariollo , based on your description, you have 17 million rows data, to improve the performance, you can consider to use the incremental refresh for the report, you can refer to the following link about it.

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

And you can use query diagnostics in power query to check, you can refer to the following link about it.

Query diagnostics - Power Query | Microsoft Learn

Best Regards!

Yolo Zhu

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

[darkniqht]

To check how much memory your query consumes in Power BI, you can use the following steps:

1. Query Diagnostics:

   • In Power Query, go to the Tools tab.
   • Select Query Diagnostics to get insights into performance, including memory usage.

2. Performance Analyzer:

   • In Power BI Desktop, go to the View tab and enable Performance Analyzer.
   • Start recording and then refresh your visuals. It will show you how long each visual takes and can give clues about resource usage.

3. Data Reduction Techniques:

   • Filter Data: If possible, filter out unnecessary rows before importing.
   • Aggregate Data: Instead of calculating on all 17 million rows, consider summarizing the data first.
   • Use DAX Measures: Perform calculations using DAX instead of Power Query, as DAX can be more memory efficient.

4. Optimize Data Types:

   • Ensure your data types are as efficient as possible (e.g., using integers instead of decimals where applicable).

5. Increase Memory Limit:

   • If you frequently hit resource limits, consider upgrading to a higher capacity with Power BI Premium.

[Mariollo]

Hey @PijushRoy your solution is rather for Desktop than Service.

For Desktop there is another solution, you can go to Options and change Query-limit simulations, but this is not what I am looking for.

[PijushRoy]

HI @Mariollo 
Can you go to Option & Settings and clear the catch, then try again
If you face the same issue, please reply