How to Convert Import to DirectQuery in Power BI
So, your Power BI report that started small and snappy is now struggling to keep up. The PBIX file takes a long time to load, and refreshing the data feels like watching paint dry. This is a common issue when your data grows, and you need it to stay fresh. This article will show you how to convert your data model from Import mode to DirectQuery and cover the key differences and potential challenges you might face along the way.
Import vs. DirectQuery: Understand What You're Changing
Before you make any changes, it’s important to understand the fundamental differences between Power BI's two main data connection modes. Choosing the right one depends entirely on your needs for data freshness, performance, and volume.
What is Import Mode?
Think of Import mode like taking a snapshot of your data. When you connect to a source in Import mode, Power BI takes a copy of your data and loads it into its high-performance in-memory engine (called the VertiPaq engine). All your charts, tables, and calculations run against this internal, compressed copy.
- Pros: Insanely fast performance for visuals and calculations because the data is self-contained. You have access to the full range of both Power Query and DAX functions.
- Cons: The data is only as fresh as your last refresh schedule. It’s a static copy. You're also limited by the size of the dataset you can import, which can soon become problematic for large datasets.
What is DirectQuery Mode?
DirectQuery functions more like a live video stream of your data. Instead of copying anything, Power BI creates a direct connection to your source database. Every time you interact with a visual - clicking a slicer, filtering a chart - Power BI queries the source database and retrieves the live results.
- Pros: Your data is always up-to-date. You can work with enormous datasets that would be impossible to import (think terabytes of data in a SQL database).
- Cons: Performance depends on the speed of your source database. Complex visuals can translate into slow queries, leading to lag. You are also restricted to a smaller subset of Power Query and DAX functions that can be translated into the native language of your data source (like SQL).
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.
When Should You Switch to DirectQuery?
Swapping a table from Import to DirectQuery is the right move when your priorities shift. Consider making the switch if:
- Your data needs to be real-time: If business decisions are being made on minute-by-minute data, scheduled refreshes just won’t cut it.
- Your dataset is massive: If you're hitting import size limits or your PBIX file has become unmanageably large, DirectQuery allows you to analyze the data without ingesting it.
- You have a powerful, well-optimized source database: If you're connecting to a fast, powerful database like Azure Synapse, Snowflake, or a well-indexed SQL Server, it can often handle live queries without breaking a sweat.
How to Convert a Table to DirectQuery: A Step-by-Step Guide
In older versions of Power BI, this was a one-way street, you had to decide at the very beginning and couldn’t change your mind. Thankfully, with the introduction of "composite models," this process is much more flexible. Here’s how you can make the change.
Before You Start: Always Back Up Your File!
This process makes significant changes to your data model. Before you do anything else, save a copy of your PBIX file with a different name (e.g., MyReport_Backup.pbix). This gives you a safe version to return to if anything goes wrong.
Step 1: Go to the Model View
To change a table’s storage mode, you need to be in the Model view in Power BI Desktop. This is the third icon down on the left-hand navigation pane, the one that looks like a database relationship diagram.
Step 2: Select Your Table and Open Properties
In the Model view, find the table you want to convert from Import to DirectQuery. Single-click on the table header in the diagram. This will bring up the Properties pane on the right-hand side of the screen. If you don't see it, go to the "View" tab in the top ribbon and make sure "Properties" is checked.
Step 3: Change the Storage Mode
In the Properties pane, scroll down to the Advanced section. You’ll see a dropdown menu labeled Storage mode. It will currently be set to "Import."
Click the dropdown and change it to DirectQuery.
Power BI will show a warning dialogue. This warning alerts you that some features, like calculated tables that rely on the converted table, might be broken because they cannot be formulated in the source database. It also informs you about potential performance implications. Since you have a backup, click OK.
Step 4: Repeat for Other Tables as Needed
You’ll have to repeat this process for every table you want to switch to DirectQuery. A handy trick is that you can select multiple tables in the Model View by holding down the Ctrl key while clicking them. Then you can change the storage mode for all of them at once in the Properties pane.
Post-Conversion: Common Challenges and How to Fix Them
Converting your table is just the first step. You'll likely encounter a few broken visuals or error messages. Don't panic, this is a normal part of the process. Here’s what to look out for.
Challenge 1: "This DAX Function is Not Supported in DirectQuery"
DirectQuery is more restrictive with Data Analysis Expressions (DAX). Because every DAX formula has to be translated into a native query for the source database (like T-SQL), certain complex or iterative functions aren't allowed. Functions related to time-intelligence or those that perform extensive table manipulation (like some uses of CALCULATETABLE) are common culprits.
- The Fix: The best solution is often to push the logic back to the data source. For example, instead of creating a complex calculated column in DAX to segment your customers, ask your database administrator to create that column directly in the source SQL View. This simplifies your DAX and improves performance.
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.
Challenge 2: Your Report is Suddenly Slow
If your visuals are taking a long time to load after the switch, it means your source database is struggling to keep up with the queries Power BI is sending it. Every slicer click, every filter change, is a new set of queries.
- The Fix: First, use the Performance Analyzer tool in Power BI (under the "View" tab). This will show you exactly how long each visual takes to render and reveals the underlying query being sent to your database. You can use this to identify the slowest visuals. Second, work with your data team to optimize the source database through things like indexing columns that are commonly used for filtering or joining.
Challenge 3: The Hybrid Approach (Composite Models)
You don't have to choose one mode for your entire report. The most effective Power BI models are often "composite models" - a hybrid mix of Import and DirectQuery.
- When to Use It: A classic example is an e-commerce dashboard. Your transactions table (sales or orders) might be billions of rows and needs to be in DirectQuery for real-time reporting. However, your supporting tables, like products, customers, or a date table, are much smaller and don't change often.
- The How-To: Leave these smaller "dimension" tables in Import mode. They will be lightning fast. Set only your massive "fact" table to DirectQuery. When you relate them in the Model View, Power BI is smart enough to manage the communication between the fast, imported tables and the live DirectQuery table. This gives you the best of both worlds: performance and data freshness.
Final Thoughts
Switching your Power BI report from Import to DirectQuery is your key to unlocking real-time analytics on huge datasets. The process itself is straightforward - just change the storage mode in the properties pane - but success depends on carefully managing DAX compatibility, optimizing your source queries, and exploring hybrid composite models for the best of both worlds.
For many teams, especially in marketing and sales, the manual work of connecting to data sources and wrangling tables in tools like Power BI can be a constant headache. That's why we created Graphed. We connect to your marketing and sales platforms in minutes and let you build real-time dashboards just by describing what you want in plain English. No more choosing between data modes or worrying about performance tuning - just ask a question and get an answer, live from your data.
Related Articles
Facebook Ads for Physical Therapists: The Complete 2026 Strategy Guide
Learn how to use Facebook ads for physical therapists to attract new patients in 2026. Complete strategy guide with targeting, ad creative, instant forms, budget guidelines, and follow-up best practices for cash-pay PT practices.
Facebook Ads for Clinics: The Complete 2026 Strategy Guide
Learn how to use Facebook Ads for Clinics to grow your patient base in 2026. Complete guide covers targeting, campaign types, and compliance requirements.
Facebook Ads for Salons: The Complete 2026 Strategy Guide
Learn how to run profitable Facebook ads for hair salons and beauty spas in 2026. This comprehensive guide covers targeting, ad creation, budgeting, and proven strategies to attract more clients.