How to Increase Per Query Memory Limit in Power BI

Hitting a memory limit error in Power BI is a common roadblock when working with large or complex datasets, but it's one you can definitely overcome. This tutorial will walk you through exactly why this error occurs and the specific steps you can take to increase the memory limit for your queries in Power BI Premium or Premium-Per-User (PPU) capacities.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Causes the "Out of Memory" Error in Power BI?

When you see an error message like "The query has run out of memory" or something similar during a data refresh, it means that a specific Power Query (M) or DAX query demanded more memory than was available for it to execute.

It's important to understand this isn't necessarily about the total RAM on your personal computer or the server. In the Power BI Service, particularly for Premium and PPU capacities, resources are managed to ensure stability. Power BI sets a "per query memory limit" as a safeguard. This prevents a single inefficient query in one dataset from consuming all the capacity's resources and causing performance issues for every other report and user on that same capacity.

This error is most common in a few situations:

• Complex Power Query Transformations: Steps like merging large tables, creating extensive conditional columns, unpivoting wide tables, or applying complicated text manipulations can consume significant memory. The Power Query engine tries to load data into memory to perform these steps, and if the working dataset is too large, it hits the limit.
• Inefficient DAX Formulas: While often associated with slow reports, very complex DAX measures can also consume massive amounts of memory during the data refresh process, especially if they involve large-scale iterators (like SUMX) over unoptimized tables or intricate relationship logic.
• High Cardinality Columns: Columns with a high number of unique values (like a transaction ID, a timestamp down to the millisecond, or user email addresses) are memory-intensive for Power BI's compression engine. When queries need to process these, memory usage spikes.
• Large Datasets: Simply put, the more data you're trying to refresh and process at once, the more memory is required.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Before You Change the Memory Limit: Optimize First

Increasing the memory limit should be a last resort, not your first step. Throwing more resources at an inefficient model is a temporary fix that can hide underlying problems and lead to slower performance down the line. Before touching any admin settings, exhaust these optimization techniques first. An optimized model might not even need the memory limit increased.

Optimize Your Data Model and Power Query Steps

Most memory issues originate here, in the Power Query Editor. This is where you can make the biggest impact on performance and memory usage.

• Remove Unnecessary Columns and Rows: This is the golden rule. If you don't need a column for any report, measure, or relationship, remove it. Use the "Choose Columns" and "Remove Columns" steps early in your query. Similarly, filter out rows you don't need - for example, if you only report on the last two years of sales, filter out all older data.
• Ensure Query Folding is Happening: Query folding is a process where Power Query translates your transformation steps (like filtering, renaming, removing columns) into a single SQL statement that is executed by the source database. This means the heavy lifting is done by the database server, and Power BI only receives the final, pre-processed data. You can check if a step is folding by right-clicking on it, if the "View Native Query" option is available, that step and the ones above it are folding. Complex transformations or custom M functions can "break" folding, forcing Power BI to download the entire table and process it in memory.
• Reduce Cardinality: If you have a column with millions of unique values, ask if it's truly necessary. For instance, a datetime column with timestamps down to the second creates massive cardinality. If you only need the date for your reports, split this into separate 'Date' and 'Time' columns and consider removing the 'Time' column if unused.
• Choose the Right Data Types: Using appropriate data types helps Power BI optimize storage and performance. Don't use "Text" for a column that only contains whole numbers, convert it to "Whole Number."

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Check Your DAX Measures

Though less common as a cause for refresh failures, complex DAX can contribute to memory pressure. Review your data model's calculated columns and measures for any major performance bottlenecks.

• Calculated Columns vs. Measures: Remember that calculated columns are computed during the data refresh and are physically stored in your model, consuming memory. If you can achieve the same result with a measure (which is calculated at query time), it's often more memory-efficient.
• Avoid Iterators Over Whole Tables: Functions ending in "X" like SUMX, AVERAGEX, and FILTER can be memory-intensive if they have to scan millions of rows in a massive table. Ensure you are applying filters or iterating over smaller, necessary portions of the table whenever possible.

Step-by-Step: How to Increase the Per Query Memory Limit

If you've optimized your model and are still hitting the memory limit, it's time to adjust the capacity settings. This requires you to be a Power BI administrator or a Capacity Administrator.

Prerequisites

• You need a Power BI Premium or Premium-Per-User (PPU) license. This setting is not available for Power BI Pro users, as they operate on a shared capacity with fixed limits.
• You must have Administrator or Capacity Admin permissions to make these changes.

Instructions:

1. Navigate to the Admin Portal: Log in to the Power BI Service (app.powerbi.com). In the top right corner, click the Settings gear icon and select Admin portal from the dropdown menu.
2. Go to Capacity Settings: On the left-hand navigation pane of the Admin portal, click on Capacity settings.
3. Select Your Capacity: You will see tabs for "Power BI Premium" and "Power BI Premium Per User." Click on the relevant tab for the workspace you are working in. Then, click on the name of the capacity you want to modify.
4. Open Workload Settings: Once you're inside the specific capacity's management screen, look for the Workloads section and expand it. This is where you control how the capacity allocates its resources for different tasks.
5. Adjust the Dataflows Memory Limit: Scroll down within the Workloads section until you find the workload for Dataflows. Here, you'll see a setting labeled Max Memory (%). This isn't the total memory for all dataflows, it's the maximum percentage of memory allocated to the dataflows workload that a single query or transformation step can use. The default is often set to 25%.
6. Increase the Value Incrementally: You can increase this percentage. Do not immediately set it to 100%. A large, sudden increase can put your capacity's stability at risk. A runaway query could crash the entire Dataflows workload. A sensible approach is to increase it incrementally. For example, try moving from 25% to 40% or 50%. Click Apply to save the changes.
7. Test the Refresh: Go back to your dataset and trigger the data refresh again. In most cases, the additional memory provided will be enough to allow the stubborn query to complete successfully.

What if I'm Using Power BI Pro or Desktop?

The "Per Query Memory Limit" setting is exclusive to Premium and PPU capacities. So, what are your options if you're a Power BI Pro user or are getting memory errors in Power BI Desktop?

• For Power BI Desktop: The primary memory limit is the available RAM on your local machine. If complex queries are failing, the root cause is almost always an unoptimized Power Query process. The optimization techniques listed earlier are your solution. Focus on reducing data size, removing columns, ensuring query folding, and simplifying transformations before the data even loads into your model.
• For Power BI Pro: You operate on a shared Microsoft-managed capacity. The memory and V-core limits are fixed and cannot be changed by users. Your only path forward is optimization. If your data model is fully optimized and still exceeds the limits of shared capacity, it may be an indicator that your project's scale now requires the dedicated resources of a Premium per User or Premium capacity.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Final Thoughts

The infamous "out of memory" error in Power BI is a clear signal that a query is demanding too many resources. Your first and most effective response should always be to optimize your Power Query steps and data model. Only after confirming your model is as efficient as possible should you consider increasing the per-query memory limit within your Premium or PPU capacity admin settings.

We know that managing, optimizing, and refreshing data from dozens of different marketing and sales platforms can make these kinds of technical hurdles a constant battle. That’s why we built Graphed. We handle all the complexities of connecting to data sources and keeping dashboards updated in real-time, so you can stop wrestling with performance tuning and focus on getting answers. You can create an entire cross-channel marketing dashboard just by describing what you need in plain English - no need to even think about memory limits or query settings.