How to Change Data Connectivity Mode in Power BI

Changing the data connectivity mode in your Power BI report can feel like flipping a hidden switch that dramatically alters how everything works. This single setting impacts your report's performance, data freshness, and even which DAX functions you can use. This guide will walk you through exactly what the different connectivity modes are, when to use each one, and the step-by-step process for changing them.

Why Data Connectivity Modes Matter

Before jumping into the "how," it's important to understand the "what." Power BI offers a few ways to connect to your data sources, and your choice has serious consequences for your report. The mode you select determines whether Power BI caches a copy of your data inside your report file or queries the data source directly every single time a user interacts with a visual.

This decision is a fundamental balancing act between performance, real-time data access, and analytical flexibility. Let's break down the main players.

Import Mode: The Fast and Flexible Standard

In Import mode, Power BI takes a copy, or a snapshot, of your data from the source and stores it directly within the .PBIX file. This data is compressed and optimized for fast querying using Power BI's internal VertiPaq engine.

When to use Import Mode:

• Your dataset is not massive (generally under 1-2 GB after compression).
• You don't need real-time data, updates via scheduled refreshes (e.g., daily or hourly) are acceptable.
• You need the absolute best performance for interacting with visuals, slicers, and filters.
• You want to use the full range of DAX functions without any limitations.
• You are merging data from multiple sources (like an Excel file and a SQL database).

What's the catch?

The main drawbacks are the file size, as the entire dataset is stored in your .PBIX file, and the data being only as fresh as your last scheduled refresh. You won't see changes that happened five minutes ago.

DirectQuery Mode: The Real-Time Contender

DirectQuery is the opposite of Import mode. With DirectQuery, Power BI does not copy the data. Instead, it only stores metadata about your table schemas. Every time you interact with a visualization — slicing, dicing, filtering — Power BI sends live queries to the underlying data source to fetch the results.

When to use DirectQuery Mode:

• You absolutely need real-time or near real-time data (e.g., for an IoT sensor dashboard or a factory monitoring system).
• Your dataset is enormous and simply too large to fit in memory or a .PBIX file (think billions of rows in a data warehouse).
• Data sovereignty rules require that the data remain in its original location.

What's the catch?

Performance is entirely dependent on the speed and efficiency of the underlying data source. A slow-running database will result in a slow-running Power BI report. Additionally, DirectQuery has limitations on the DAX functions you can use, especially for time intelligence formulas, and complex data transformations in the Power Query editor are more restricted.

Dual Mode: The Best of Both Worlds

Dual mode, used within a "composite model," lets you have the best of both worlds within a single report. A table set to Dual mode can act as either Import or DirectQuery depending on the query. Dimension tables (like a calendar table or product lookup table) are often set to Dual. When you query a visual that only uses columns from Dual tables, Power BI behaves like Import mode for maximum speed. But when you include a column from a DirectQuery fact table (like a huge sales transaction table), the Dual tables will behave as if they're in DirectQuery mode to interact with it.

When to use Dual Mode:

• You have a mixed model where some tables are massive (DirectQuery) and others are small lookup tables (which you'd prefer to be in Import).
• You want to optimize slicer performance. Slicers populated from a Dual-mode dimension table will be fast and responsive because they can be cached.

What's the catch?

It adds a layer of complexity to your data model. You have to be thoughtful about relationship management and understand how queries will behave to avoid performance bottlenecks.

How to Change the Data Connectivity Mode for a Table

Now for the main event. You can change the storage mode for individual tables (as long as your source supports DirectQuery), transforming a standard Import model into a more flexible Composite model. This is particularly useful when you initially import everything but soon discover one table is growing too large and needs to be switched over to DirectQuery.

The change is made in the Model view of Power BI Desktop.

Step-by-Step Instructions

1. Navigate to the Model View: In the Power BI Desktop application, look at the left-hand navigation bar. Click on the icon that looks like a database schema or diagram. This will take you to the Model view where you can see all of your tables and their relationships.
2. Select the Table You Want to Change: In the Model view, click on the header of the table whose storage mode you wish to modify. This will select the entire table, and its properties will become available for editing.
3. Open the Properties Pane: If it's not already visible, go to the "View" tab on the ribbon at the top and make sure the "Properties" pane is checked. This pane typically appears on the right-hand side of the screen.
4. Find the Storage Mode Setting: With your table selected, scroll down in the Properties pane until you find the "Advanced" section. Inside this section, you'll see a dropdown menu labeled Storage mode. (Note: If this option is grayed out, it likely means your data source does not support DirectQuery connectivity.)
5. Choose the New Mode: Click the dropdown and select your desired mode: Import, DirectQuery, or Dual. After making your selection, Power BI will display a warning message. This warning is important — it alerts you to potential consequences, such as the fact that calculated tables will be removed if they reference a table you're switching to DirectQuery. Read it carefully and click "OK" if you're ready to proceed.

That’s it! Power BI will now process the change. Depending on the size of the table and the complexity of your model, this might take a few moments.

Important Considerations and Best Practices

Changing your storage mode isn't something to be done lightly. It has far-reaching effects on your entire report. Here's what you need to keep in mind.

The Impact on Performance

Switching from Import to DirectQuery will shift the performance burden from Power BI's internal engine to your source database. Before making the switch, ensure your database is optimized for the kind of analytical queries Power BI will generate. A poorly indexed database will bring your report to a crawl. Conversely, if a DirectQuery report is too slow, switching smaller tables over to Import or Dual mode can dramatically improve responsiveness, especially for slicers and filters.

DAX and Power Query Limitations

Remember that DirectQuery limits some of the functions you can use. Many of the most powerful time intelligence functions in DAX rely on a cached, static copy of data and won't work in DirectQuery mode. Furthermore, very complex transformations in Power Query may not be "folded" into a native query that can be sent to the data source, which can result in errors or poor performance. Always test your existing DAX measures and Power Query steps after changing a table's storage mode.

Building Composite Models Intelligently

The ability to mix and match modes opens up powerful possibilities. A common and highly effective pattern is the "star schema" composite model:

• Fact Table (e.g., a massive sales transactions table): Keep this table in DirectQuery mode due to its size.
• Dimension Tables (e.g., Calendar, Products, Customers): Set these smaller, less frequently updated tables to Dual mode.

This allows slicers built on your Products or Calendar tables to be incredibly fast (since they are queried from a cache), while still allowing visuals to get live, up-to-the-minute results from your sales transactions table when needed.

Final Thoughts

Understanding and changing data connectivity modes is a core skill for leveling up your Power BI development. It's the key to building reports that are both scalable and performant by finding the right trade-off between blazing-fast imported data and up-to-the-second live queries.

As powerful as these tools are, setting up pipelines and managing data models can be time-consuming, especially when your data is spread across different platforms. We built Graphed because we know firsthand how much effort goes into merely connecting and preparing data before the real analysis can even begin. By connecting directly to sources like Google Analytics, Shopify, and Salesforce and letting you build dashboards with simple, natural language, we help you get from raw data to actionable insights in seconds, not hours.