How to Enable Direct Query in Power BI

Wrangling massive datasets in Power BI can feel like trying to fit an elephant into a Mini Cooper - it's just not going to work with the standard approach. That's where Direct Query comes in, allowing you to connect to your data live without importing it first. This tutorial breaks down exactly what Direct Query is, when you should use it, and how to enable it step-by-step.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Understanding Direct Query vs. Import Mode

Before flipping any switches, it’s important to understand the fundamental choice you're making in Power BI between two primary data connectivity modes: Import and Direct Query. Your decision here impacts everything from performance to data freshness.

Import Mode: The Default Experience

Import mode is Power BI’s default and most common setting. When you use Import mode, Power BI pulls a full copy of your data from the source and stores it inside your Power BI file (the .PBIX file). This data is cached in memory, which makes it incredibly fast.

• How it works: You schedule a data refresh (e.g., once a day, every hour) to pull fresh data from the source and update the cached copy.
• Pros: Excellent performance for visuals and calculations because the data is stored locally. You have full access to all Power Query transformations and DAX functions.
• Cons: Limited by your computer's memory and the file size limits of Power BI Service (typically 1 GB per dataset for Pro users). The data is only as fresh as your last refresh. This isn't ideal for real-time reporting.

Direct Query: The Live Connection

Direct Query takes a completely different approach. It does not store a copy of the data in your Power BI file. Instead, it only stores the metadata - the table names, column names, and relationships. Every time you interact with a visual in your report, Power BI sends a query directly back to the source database to get the latest data.

• How it works: You click a slicer, and Power BI instantly writes and sends a SQL query (or equivalent) to your database. The database processes the query and sends a small, aggregated result set back to be displayed in your visual.
• Pros: It supports massive datasets that would be impossible to import. The data displayed is always up-to-date, making it perfect for near real-time dashboards. It also respects any security rules set at the database level.
• Cons: Report performance is entirely dependent on the speed of the underlying data source. A slow database means a slow report. It also has some limitations in Power Query transformations and the DAX functions you can use.

When to Bother with Direct Query

So, when do you pick the more complex path of Direct Query? Here are the most common scenarios:

• Your dataset is enormous. If your data source is measured in the tens of billions of rows or hundreds of gigabytes, importing it isn't an option. Direct Query is built for this scale.
• You need near real-time data. For dashboards tracking manufacturing lines, stock market data, or live service metrics, waiting for an hourly refresh won't cut it. Direct Query gives you a constantly current view.
• Complex data source security is in place. If your database uses row-level security to control what different users can see, Direct Query can pass the user’s credentials through to honor those permissions automatically.

What You Need Before You Start

Excited to get started? Not so fast. Direct Query has a few important prerequisites. Make sure you have these sorted out before you proceed to avoid frustration down the line.

Graphed

Still Building Reports Manually?

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

Watch the Demo

A Supported Data Source

Direct Query doesn't work with every data source. It is designed for structured relational databases that can handle the constant barrage of queries a busy report will generate. A static Excel file or CSV is not a candidate. Common supported sources include:

• Amazon Redshift
• Azure SQL Database & SQL Data Warehouse (Synapse Analytics)
• Google BigQuery
• Oracle Database
• PostgreSQL
• Snowflake
• SQL Server
• And many more...

Always check Microsoft's official documentation for an up-to-date list of Direct Query-enabled sources.

A Well-Performing Database

This is the most critical factor. Your Power BI report will only be as fast as your source database. If your database takes 30 seconds to return a query, your Power BI visual will take 30 seconds to load. You need a properly structured and optimized database. This often means working with your IT or data engineering team to ensure tables are indexed correctly and that the database has enough resources to serve your report.

How to Enable Direct Query in Power BI (Step-by-Step)

With the theory and prerequisites covered, let's walk through the actual steps in Power BI Desktop. The process itself is surprisingly straightforward.

Step 1: Open Power BI and Select "Get Data"

Launch Power BI Desktop. From the Home ribbon, click on the "Get Data" button to open the connector gallery.

Step 2: Choose Your Supported Data Source

From the gallery, select the database you want to connect to. For this example, we’ll use SQL Server, as it's one of the most common sources for Direct Query.

Step 3: Enter Your Server and Database Details

A dialog box will pop up asking for the server and database information.

Fill in the Server name and, optionally, the Database name if you know it. This is where you’ll see the crucial options for connectivity mode.


Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Select "DirectQuery" Mode

Right below the database details, you’ll see the Data Connectivity mode options: Import and DirectQuery. Click the bubble next to DirectQuery.

Click "OK." You may be prompted to enter your credentials for the database if you haven't connected to it before.

Step 5: Load Your Desired Tables

After a successful connection, the Navigator window will appear, showing you all the schemas, tables, and views available in your database. Select the tables you need for your report by checking the boxes next to them. You'll see a preview of the data on the right.

Click "Load" to bring the table metadata into your Power BI model. Note that clicking "Transform Data" can take you to the Power Query Editor, but the available transformations will be more limited than in Import mode.

And that’s it! You are now operating in Direct Query mode. You’ll notice in the bottom right corner of your Power BI window it says "Storage Mode: DirectQuery."

Best Practices for Using Direct Query Effectively

Simply enabling Direct Query doesn't guarantee a great report. Your job now shifts from data wrangler to performance optimizer. Here are some best practices to follow.

Keep the Model 'As-Is' as Much as Possible

Direct Query loves relationships "in the source". To the extent possible, ensure you've done this. When you avoid making changes, Power BI's Query Folding keeps queries native. When you make changes beyond your ability to write M Query language back, Power Query stops working efficiently.

Don't Go Overboard with Visuals

Remember, every single visual on your report page sends at least one query (and often more) to your data source. A page with 15 complex visuals can grind your database and your report to a halt. Be intentional with what you display, focusing on key performance indicators (KPIs) and summarized data.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Optimize Measure and Visual Calculations

DAX's IF() or SWITCH() are great tools. However, use simple addition, subtraction, and limited iterator functions like SUMX(). These functions keep load reasonable, even on billions of rows, by optimizing the queries to require small amounts of data. Using a Time/Date field? Try using relative filtering which will also hardcode into your DAX (like it was native). Using parameters is also a great way to enable users to create "self-service" like features at the query level. Your engineers will thank you for this a lot.

Talk to Your Engineers and Data Architects

Just talk to your team! Your engineers can create optimized aggregated or analytical tables. Use those new connections versus building into semantic links on your own.

Final Thoughts

Choosing Direct Query over Import is a necessary trade-off when dealing with huge datasets or the need for live, up-to-the-minute data. While it demands a well-optimized source database and a more careful approach to report design, it unlocks capabilities that are simply impossible with the standard import method.

Wrestling with connection modes, DAX limitations, and performance tuning in tools like Power BI is a common experience, but it also reflects a larger challenge. At Graphed, we felt that getting answers from your sales and marketing data shouldn't require learning a complex new piece of software. We built a platform that connects directly to sources like Google Analytics, Shopify, and Salesforce, handling the heavy lifting of data pipelines and modeling for you. Instead of tweaking visual queries or scheduling refreshes, you simply ask questions in plain English and get back live, interactive dashboards that are always up to date.