How to Change Storage Mode in Power BI

Switching a table's storage mode in Power BI can dramatically improve your report's performance, but it isn't always as simple as clicking a button. Making the wrong change can break relationships or slow your report to a crawl. This article breaks down what storage modes are, why you should care, and how to safely change them step-by-step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Are Power BI Storage Modes?

Before changing anything, it's important to understand the three primary storage modes in Power BI. Each mode tells Power BI how to handle the data for a specific table, and choosing the right one is a balancing act between performance, data freshness, and model size.

Import Mode

This is the default and most common storage mode. When you use Import mode, Power BI loads a complete copy of the data from your source into your .PBIX file. This data is then compressed and stored in-memory.

• Pros: incredibly fast query performance because the data is cached right in the report. Supports the full range of DAX functions and Power Query transformations.
• Cons: Data is only as fresh as the last refresh. Storing all the data can lead to large file sizes and high memory consumption, which can be an issue in the Power BI Service.
• Best for: Smaller dimension tables (like a product list or customer directory) and fact tables that don't need real-time updates.

DirectQuery Mode

In DirectQuery mode, Power BI does <em>not</em> store a copy of the data. Instead, it sends queries directly to the source database every time a user interacts with a visual. Think of this as a live, direct connection to your data.

• Pros: The data is always up-to-date, reflecting the current state of the source database. It’s excellent for very large datasets that won't fit in memory.
• Cons: Performance is entirely dependent on the speed of the source database. If your database is slow, your report will be slow. There are also some limitations on the Power Query transformations and DAX functions you can use.
• Best for: Extremely large fact tables (billions of rows) or scenarios where real-time data is absolutely required, like monitoring a live production line.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Dual Mode

Dual mode is the flexible hybrid. A table set to Dual mode can act as either Import or DirectQuery, depending on the other tables involved in a query. Power BI intelligently chooses the most efficient mode for each visual.

• Pros: Offers the best of both worlds. It can interact with fast Import tables using the in-memory cache and query DirectQuery tables live at the source. This can significantly improve performance in a mixed-mode model.
• Cons: Adds a layer of complexity to your model. You need to understand how the tables interact to get the most out of it.
• Best for: Frequently used dimension tables (like a 'Date' table) that need to connect to both huge DirectQuery fact tables and smaller Import fact tables within the same report.

Why Would You Change a Table's Storage Mode?

You can greatly improve your reports and dashboards by strategically changing storage modes to fit your needs.

• Optimize Performance: The most common reason. Maybe your report is sluggish because a massive transaction table is in Import mode, consuming all your memory. Switching it to DirectQuery can make interactions snappy again. Conversely, if a Dimension table in DirectQuery is being queried constantly, switching it to Import will drastically speed things up.
• Balance Data Freshness and Speed: A sales manager might need to see transaction data in real-time (DirectQuery), but doesn't need the list of sales reps (which changes rarely) to be queried live. Setting the transaction table to DirectQuery and the sales rep table to Import provides a perfect balance.
• Reduce Model Size: Import models can grow to several gigabytes, making them difficult to manage and publish. Moving your largest table to DirectQuery can shrink a .PBIX file from 2GB to 50MB, making refreshes faster and consuming fewer resources in the Power BI Service.

Before You Begin: A Quick Checklist

Changing storage modes can have cascading effects. Before you click anything, run through this quick checklist to avoid headaches.

1. Backup Your File: This is a non-negotiable step. Save a copy of your .PBIX file before you start making changes. If anything goes wrong, you can always revert to the working version.
2. Understand the Relationship Rules: You cannot create a relationship between an Import table and a DirectQuery table in all scenarios. If related tables have incompatible storage modes, Power BI might force them all into DirectQuery or prevent the change. A "strong" relationship is preferred, which often means setting a common dimension table to 'Dual'.
3. Check Data Source Compatibility: Not all data sources support DirectQuery. SQL Server, for example, is great for DirectQuery, while an Excel file is not. Power BI will usually tell you this when you first connect, but it's good to keep in mind.
4. Consider Model Complexity: If your model has dozens of tables with intricate relationships, start small. Change one table, test your report visuals thoroughly, and then move to the next.

How to Change Storage Mode: A Step-by-Step Guide

Once you’ve gone through the checklist and are ready to proceed, changing the storage mode for a table in Power BI Desktop is straightforward.

Step 1: Open the Model View

In Power BI Desktop, look at the three icons on the far left of your screen. Click the third one, which looks like a diagram of connected boxes. This is the Model View, where you can see all the tables in your model and the relationships between them.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Select the Table to Change

Find the table whose storage mode you want to adjust and click on it. The table will be highlighted to show it’s selected.

Step 3: Access the Properties Pane

On the right side of the screen, you'll see the Properties pane. If it's not visible, go to the "View" tab in the ribbon at the top and check the box for "Properties."

Step 4: Expand the 'Advanced' Section

Within the Properties pane, scroll down until you see a section named Advanced. Click the small arrow to expand it if it's not already open.

Step 5: Select the New Storage Mode

The first option in the Advanced section is Storage mode, which will have a dropdown menu. Click this dropdown and select your desired mode: Import, DirectQuery, or Dual.

Step 6: Handle the Pop-up Warnings

Upon selecting a new mode, Power BI may show you a pop-up window with a warning. Read this carefully!

• If you are changing from Import to DirectQuery, it will warn you that all the data currently cached in the model for that table will be deleted.
• It may also warn you about "limited relationships" or that other tables in your model will be impacted. For instance, if you change table A from Import to DirectQuery, any related table B might be automatically set to Dual mode to maintain a valid relationship.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 7: Confirm and Apply the Changes

If you understand the implications and agree, click "OK". Power BI will then process the change. Depending on your model's size and complexity, this could take a few seconds or a couple of minutes.

Troubleshooting Common Issues

Sometimes things don't go as planned. Here are a few common issues and how to fix them.

• "The Storage Mode dropdown is grayed out." This usually happens for one of two reasons. Either your data source (like a CSV or Excel file) doesn't support DirectQuery, or the table is involved in complex DAX or Power Query logic that forces it into Import mode.
• "My formulas broke after the change." DirectQuery has some DAX limitations, especially with time intelligence functions that rely on a complete data history. Check if any DAX measures used by your visuals are now showing errors.
• "My report got even slower!" Shifting to DirectQuery offloads the work to your source database. If that database isn't optimized for quick query responses, your report performance will suffer. This is an issue with your database, not with Power BI.
• "A bunch of my other tables changed modes." This is that "cascading effect" we talked about. Power BI attempts to maintain valid relationships, so it automatically sets related dimension tables to Dual mode to act as a bridge. This is usually intended behavior and is good for performance.

Final Thoughts

Getting comfortable with Power BI’s storage modes - Import, DirectQuery, and Dual - is a critical step in moving from a beginner to an advanced user. By understanding when and how to change modes, you can perfectly balance report performance, data freshness, and model size to create a seamless experience for your users.

Manually optimizing models and wrestling with individual table settings is a common time-sink, especially when you have data coming from multiple places. We created Graphed to remove this friction. After connecting your different marketing and sales platforms, you can simply ask for what you need - for instance, "Create a dashboard comparing Facebook Ads spend vs revenue from Shopify by campaign" - and we instantly generate the report with real-time data, completely bypassing the complexity of storage modes and backend configurations.