How to Remove DirectQuery in Power BI

If your Power BI report is agonizingly slow, your DirectQuery connection is a likely suspect. While useful for real-time data and massive datasets, it can make report interactions feel sluggish. This article will guide you step by step on how to switch Power BI from DirectQuery to Import mode, boosting your report’s performance and unlocking its full analytical power.

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

Understanding DirectQuery vs. Import Mode

In Power BI, the way you connect to a data source is called the storage mode. The two primary modes are DirectQuery and Import, and choosing the right one is fundamental to building an effective and performant report. Understanding their differences is the first step to knowing why and when you should make the switch.

What is DirectQuery?

DirectQuery mode doesn't load any data into your Power BI file. Instead, it maintains a live connection to your source database. Every time you interact with a visual — clicking a slicer, filtering a chart, or even just opening the report — Power BI sends a query back to the source data system. The visual then updates with the freshly retrieved results.

• Pros: Excellent for extremely large datasets (terabytes of data) that won't fit in memory. It's also ideal when you need to see data in real-time or as close to it as possible.
• Cons: Performance is completely dependent on the underlying data source's speed. A slow database means a slow report. This mode also brings limitations to Power Query transformations and DAX functions you can use.

What is Import Mode?

Import mode, as the name suggests, imports and compresses a full copy of the data into your Power BI file itself (the .pbix file). The data is then stored in Power BI's high-performance, in-memory analytical engine, called VertiPaq. All interactions with visuals query this internal engine, not the original data source.

• Pros: It's incredibly fast. Because the data is held in-memory and optimized for analysis, visual interactions are almost instantaneous. You also get access to the full suite of Power Query and DAX functionalities.
• Cons: Your data is only as fresh as the last refresh. There is also a data model size limit depending on your license type (e.g., 1 GB per dataset on a Pro license). Regular refreshes are required to update the data.

Side-by-Side Comparison

Here’s a quick breakdown of how the two modes stack up:

• Performance: Import is generally much faster for report interactions. DirectQuery's speed is tied to the source system.
• Data Size: DirectQuery supports datasets far larger than Power BI's memory limits. Import is limited by the model size capacity (based on your license).
• Data Freshness: DirectQuery offers near real-time data. Imported data is a snapshot that must be updated via a scheduled refresh.
• DAX & Power Query: Import mode supports all DAX functions and M-language transformations. DirectQuery has notable limitations, especially with time-intelligence functions.
• Source System Load: DirectQuery constantly sends queries, which can tax a production database. Import only queries the source during a data refresh.

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

Why Switch from DirectQuery to Import Mode?

While DirectQuery has its place, many reports are built with it unnecessarily, creating a poor user experience. If your dataset is a manageable size (say, under a few gigabytes), you stand to gain significant benefits by switching to Import mode.

Dramatically Better Performance: This is the number one reason to switch. Users want reports that respond instantly. By moving the data into Power BI’s own high-speed VertiPaq engine, you separate your report’s performance from the health of the source database. Slicing, dicing, and filtering will feel responsive and fluid.

Unlock Full Analytical Capability: Have you ever tried to use a helpful DAX time-intelligence function like DATESMTD or TOTALQTD only to find it doesn't work? That’s likely because of a DirectQuery limitation. Switching to Import mode removes these restrictions, giving you the complete analytical toolbox that Power BI offers.

Work Offline and On the Go: Since the data is stored within your .pbix file in Import mode, you can continue to develop your report even without a live internet connection to your database. This is a game-changer for analysts who work from multiple locations or on the go.

Reduce Strain on Your Databases: Every click in a DirectQuery report hits your source data system. For busy production databases, this constant stream of analytical queries can cause performance issues for the primary applications using it. Importing the data means you only connect to that database periodically during a scheduled refresh, dramatically reducing the analytical load.

Step-by-Step Guide to Change from DirectQuery to Import

Ready to make the switch? The process is straightforward and happens directly within Power BI Desktop. Make sure to save a backup of your file before you begin.

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

Step 1: Confirm Your Current Storage Mode

Before you change anything, let’s confirm you are using DirectQuery. Open your file in Power BI Desktop. In the bottom-right corner of the status bar, you will see text that says Storage Mode: DirectQuery followed by the name of your source. If you see "Import" or nothing at all, your report is likely already in Import mode.

Step 2: Go to the Model View

On the left-hand side of the Power BI window, click on the icon that looks like a data model diagram. This is the Model view, where you'll be able to see the tables and relationships in your model.

Step 3: Change the Storage Mode for a Table

Now you'll change each table from DirectQuery to Import one by one or by multi-selecting them.

1. In the Model view, click on the table you want to switch. Hold down the Ctrl key to select multiple tables at once.
2. With the table(s) selected, look for the Properties pane on the right side of the screen.
3. Scroll down in the Properties pane and expand the Advanced section if it isn't already.
4. Here you'll see a dropdown menu labeled Storage mode. It will currently be set to "DirectQuery."
5. Click the dropdown and select Import.

Power BI will display a warning message. It will inform you that this change can't be undone and may take some time as it needs to fetch all the data from the source and load it into your file. Visuals that use this table may also break temporarily. This is normal. Click OK to proceed.

Step 4: Repeat for All DirectQuery Tables

Repeat the previous step for every table in your model that is on a DirectQuery source. You want to ensure that all tables you need are set to Import mode to fully reap the performance benefits.

Step 5: Verify the Switch

Once you’ve switched all relevant tables, look back at the bottom-right status bar in Power BI Desktop. The "Storage Mode: DirectQuery" message should be gone. Your visuals should populate with data, and you’ll likely notice that interacting with them is much faster. Congratulations, you’ve successfully removed DirectQuery!

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

Beyond Import: Understanding Dual Mode and Composite Models

What if you have one absolutely massive fact table (like transactions) but smaller, manageable dimension tables (like products or customers)? In this scenario, switching everything to Import might not be feasible. This is where composite models and the "Dual" storage mode come into play.

When you have a mix of Import and DirectQuery tables in the same model, it’s called a composite model. To optimize performance in this scenario, Power BI offers a third storage mode: Dual.

Setting a table's storage mode to Dual allows Power BI to intelligently decide whether to act as Import or DirectQuery based on the query. For example, if you have a visual that only uses a Dual-mode dimensions table, Power BI will use its cached, imported data. If another visual joins that same dimension table to a large DirectQuery fact table, Power BI will switch the dimension table to operate in DirectQuery mode for that specific query. This provides flexibility, performance for some visuals, and the ability to interact with massive tables as needed.

Next Steps: Setting Up a Scheduled Refresh

Your report is now fast and powerful, but your data is static. Since you're no longer connected live to the source, you need to tell Power BI when to pull in fresh data. This is done through a scheduled refresh in the Power BI Service.

1. Publish your report: Once you are happy with the changes in Power BI Desktop, publish the report to your desired workspace in the Power BI Service.
2. Go to Dataset settings: In the Power BI Service, navigate to the workspace, find the dataset associated with your report (it will have the same name), click the ellipsis (...), and choose Settings.
3. Configure credentials: Under Data source credentials, make sure you have entered the correct credentials for Power BI to access your source data system.
4. Set up a scheduled refresh: Expand the Scheduled refresh section. Toggle it on, select your desired refresh frequency (e.g., Daily or Weekly), set a time zone, and add one or more times for the refresh to occur. Depending on your license, you can schedule up to 8 refreshes per day (Pro) or 48 per day (Premium).
5. Apply: Click Apply to save your settings. Your dataset will now automatically update according to your schedule, keeping your report data fresh.

Final Thoughts

Switching your Power BI report from DirectQuery to Import mode is a direct path to better performance and unlocks the platform's full analytical features. After changing each table's storage mode and verifying the impact, the crucial final step is to configure a scheduled refresh in the Power BI Service to ensure your insights remain current.

Managing data connections, storage modes, and refresh schedules is time-consuming manual work, especially when your data is spread across different platforms. We built Graphed to cut through this complexity. You just connect your sources once, then ask questions in plain English to instantly build real-time dashboards and reports. This eliminates the need to worry about storage modes, data wrangling, or manual refreshes, giving you back time to focus on making data-driven decisions.