How to Use DirectQuery in Power BI

Working with massive datasets or needing up-to-the-second information in your reports can feel like a major challenge. Power BI's DirectQuery mode is designed for just these situations, allowing you to connect directly to your data source without importing everything first. This article will walk you through what DirectQuery is, when to use it, and how to optimize it for the best performance.

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 is DirectQuery? (And How is it Different from Import Mode?)

In Power BI, you have two primary ways to connect to your data: Import mode and DirectQuery mode. Understanding the difference is the first step to building better, more efficient reports.

Import Mode: The Default Choice

When you use Import mode, Power BI pulls a full copy of your data from the source and stores it inside the Power BI file (.pbix). This data is compressed and cached in memory using Power BI's high-performance VertiPaq analysis engine.

• Pros: Excellent performance. Because the data is stored in memory, calculations and visual interactions are incredibly fast. You also have access to the full suite of Power BI and DAX functions.
• Cons: The size of your dataset is limited by your system's memory and Power BI's dataset size limits (e.g., 1 GB for Pro, larger for Premium). Data is only as fresh as your last refresh schedule, meaning it’s not real-time.

DirectQuery Mode: The Live Connection

With DirectQuery mode, Power BI does not store a copy of the data. Instead, it only stores the metadata - the table names, column names, and relationships. It acts as a visualization layer on top of your data source. Each time you interact with a report (like changing a slicer or filtering a chart), Power BI translates that interaction into a query and sends it directly to the source database to get the results.

• Pros: It allows you to work with extremely large datasets (terabytes or more) that would be impossible to import. Since it queries the source directly, reports always show the latest data, which is perfect for near real-time analytics.
• Cons: Performance is entirely dependent on the speed of the underlying data source. If your database is slow to respond to queries, your Power BI report will be slow. Additionally, there are some limitations in Power Query transformations and DAX functions compared to Import mode.

Here's a quick cheat sheet to tell them apart:

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

When Should You Use DirectQuery?

While Import mode is generally recommended for its speed, DirectQuery shines in specific scenarios. You should consider using it when:

• Your data volumes are massive. If your dataset is too big to fit into a Power BI model - think billions of rows of retail transactions, real-time IoT sensor data, or extensive web logs - DirectQuery is the only way to analyze it without pre-aggregating the data.
• You need near real-time reporting. For monitoring systems, factory floor dashboards, or financial trading reports, showing data that’s just a few minutes old is unacceptable. DirectQuery provides an always-on connection to live data.
• Data sovereignty rules apply. In some industries or regions, regulations prohibit data from being moved or stored in a separate location. DirectQuery allows you to analyze your data securely while leaving it in the source database.

How to Connect Using DirectQuery in Power BI

Setting up a DirectQuery connection is straightforward. You select it during the initial connection process in Power BI Desktop.

Step 1: Get Data From the Home ribbon, click on Get data and select your data source. DirectQuery is only available for sources that support it, which are typically relational databases like SQL Server, a Snowflake data warehouse, or a service like Databricks.

Step 2: Choose Your Connection Mode In the connection dialog box for your source, you'll see options for Data Connectivity mode. This is where you make the choice. Select DirectQuery instead of Import.

Step 3: Load Your Data & Build Your Model Once you click OK, you can select the tables you need in the Navigator window, just as you would with an Import connection. The key difference is that when you click "Load," only the schema (table and column info) is loaded into your model, not the actual data rows.

You can then build relationships in the Model view just as you normally would. Your report canvas and fields list will look and feel the same. The difference happens behind the scenes every time you drag a field onto a visual.

Best Practices for Optimizing DirectQuery Performance

Because DirectQuery relies on your source database, a poorly optimized model or report will create slow, frustrating user experiences. Here’s how to make it fast.

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

1. Optimize the Source Database

This is the most critical factor. Power BI can only be as fast as the source responding to its queries. Work with your database administrators to ensure:

• Indexes are in place: Columns that are frequently used for filtering, slicing, or in relationships should be properly indexed. This is the single biggest performance boost you can make.
• Statistics are up-to-date: The database engine needs current statistics to create efficient query execution plans.
• Sufficient hardware: Your source database needs adequate CPU, memory, and I/O capacity to handle the analytical queries Power BI will be sending its way.

2. Keep Power Query Transformations Simple

In Import mode, you can perform complex transformations in the Power Query Editor. In DirectQuery, Power BI tries to translate those transformations into a single, native query for the source system - a process called query folding. Complex steps (like a complex custom M function) can break query folding, forcing Power BI to pull chunks of data and process it locally, which is incredibly slow. Stick to simple transformations like renaming or removing columns that can be easily translated.

3. Be Mindful of How Many Visuals You Use

Every single visual on a report page generates at least one query to the source database. Ten visuals on the page? At least ten queries are fired off every time someone opens the report or changes a filter.

• Start with less: Build your report with essential visuals first and monitor performance.
• Use query reduction options: Go to File > Options and settings > Options > Query reduction. Here you can change slicers to have an "Apply" button instead of instantly sending queries for every single change. This is a game-changer for report usability.

4. Understand DAX Limitations

Many DAX functions are optimized for Power BI's in-memory engine. When using DirectQuery, Power BI has to convert your DAX formulas into equivalent SQL code. This works for many functions but can be slow or unsupported for others.

Pay special attention to date intelligence functions like DATESYTD or SAMEPERIODLASTYEAR. While many now work in DirectQuery, they often perform better with a dedicated Date table in your source and simpler DAX like CALCULATE() with explicit filters.

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

5. Consider Composite Models: The Best of Both Worlds

What if you have one massive fact table but several smaller, related dimension tables? This is a perfect use case for a Composite Model. A Composite Model allows you to set the storage mode individually for each table in your model.

You could set your 10-billion-row SalesTransactions table to DirectQuery mode to get near real-time data without importing it. At the same time, you could set your smaller Product, Store, and Date dimension tables to Import mode.

This hybrid approach gives you amazing performance. Slicers based on the imported Product or Store tables will be lightning-fast because the data is cached, but when you interact with the main sales visual, it will still query the live data from the source, combining bits from both worlds.

Final Thoughts

DirectQuery is a powerful feature in Power BI, enabling analysis on massive and fast-changing datasets that would otherwise be out of reach. Succeeding with it requires a shift in mindset - you aren't just building a Power BI report, you're building a visual front end for your source database, so performance and optimization at the source are paramount.

That technical overhead is exactly what keeps many marketing and sales teams from building the dashboards they need. If you're tired of wrangling data configurations, learning DAX, and worrying about database optimization, we've made the process much simpler. With Graphed, you can securely connect to sources like Google Analytics, Shopify, and Salesforce in seconds. Just ask a question in plain English like, "Show me a dashboard of ad spend vs. revenue by campaign," and we create a real-time, interactive dashboard for you instantly - no storage modes to configure, no tables to model.