Ravindra BagaleCourses & study guides मराठी Track your progress

4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection

4.6 DirectQuery in Power BI: How It Works

By Last updated:

Start with a problem

Salman's DirectQuery page has 4 visuals. Every slicer click sends queries to the ops database, and the DBA complains.

Explain simply

In DirectQuery, Power BI stores only the model; every visual sends SQL when it loads or a filter changes. Use Query reduction (Apply buttons), fewer visuals, and Dual mode for small dimensions.

Show the data

Item Value
Visuals on the page 4 (card, bar, line, table)
Slicer clicks while choosing 3 cities 3
Queries per change about 1 per visual

About this data: This table uses columns: Item, Value. This table uses columns: Storage modes. This table uses columns: Queries sent. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: quickcommerce_practice.sql (source: src/practice/).

Demonstrate

  1. Get data › SQL Server › DirectQuery › tick orders and dark_store › Load.

  2. Model view › dark_store › Properties › Storage mode Dual.

    Your result should look like this:

    Storage modes
    orders: DirectQuery · dark_store: Dual

  3. Optimize › Performance analyzer › Start recording › click the slicer 3 times.

    Your result should look like this:

    Queries sent
    about 3 clicks × 4 visuals = 12 refresh rounds

  4. File › Options › Current file › Query reduction › Add an Apply button to each slicer.

    Your result should look like this:

    Queries sent for the same choice
    about 1 × 4 = 4

  5. Check the DirectQuery time of each visual in Performance analyzer.

Show the result

Your result should look like this (fictional sample):

Setting Visual refreshes for choosing 3 cities
No Apply button ≈ 12 (3 clicks × 4 visuals)
Apply button ≈ 4 (1 apply × 4 visuals)

Explain what happened

Each interaction re-queried every visual. The Apply button turns three clicks into one change, so the source gets about a third of the work. Dual lets small dimensions answer from memory when possible.

Explore further

  • Use Optimize › Optimization presets for a quick query-reduction setting.

Detailed reference for this lesson:

With DirectQuery, Power BI stores only the model structure (tables, relationships, measures). Each time a visual loads or a slicer changes, it sends SQL to the database.

Steps in Power BI

  1. Get data › SQL Server › Data Connectivity mode: DirectQuery › OK.
  2. Select tables › Transform Data (only transformations that fold are allowed. Otherwise you see This step results in a query that is not supported in DirectQuery mode).
  3. In Model view, select a table › Properties › Advanced › Storage mode to see DirectQuery. For small dimension tables you can choose Dual (composite model).
  4. In Report view, use Optimize › Optimization presets or File › Options and settings › Options › Current File › Query reduction to reduce the number of queries sent (for example add Apply buttons to slicers and filters).
  5. Use Optimize › Performance analyzer to see the DirectQuery time of each visual.

Ravindra Bagale's Tip

Friends, many students choose DirectQuery for a small dataset and then write heavy row-by-row DAX that turns into slow SQL. Use DirectQuery only when you really need it, keep measures simple, and talk to the DBA first, because every click in the report sends queries to the production database. Clear?

Fix common mistakes

Wrong result Reason Correction
Very slow visuals on a small dataset DirectQuery chosen without need Use Import
Heavy row-by-row DAX is slow It becomes complex SQL Simplify measures; push logic to the source
"Not supported in DirectQuery" step error Transformation does not fold Do it in a view or switch to Import

Practise independently

Practice task

A page has 6 visuals and users make 5 slicer changes. Roughly how many visual queries run without and with Apply (assuming they apply once)?

Hints
  • Visuals × changes.
Solution

Without Apply: about 6 × 5 = 30; with one Apply: about 6.

Think like a professional

In DirectQuery, design for the database: fewer visuals per page, Apply buttons, and aggregated views.

More worked examples: how DirectQuery works in Power BI

Suppose we are at Swiggy. The live orders table in SQL Server gets thousands of new rows every minute. With Import, Power BI keeps a copy and you see new orders only after a refresh. With DirectQuery, Power BI keeps no copy. Each time you click a visual, it sends a query to SQL Server and shows the answer from there.

Example 1 (simplest): choose DirectQuery

Connect to the SQL Server orders database.

Steps:

  1. Click Get data, then SQL Server.
  2. Type the server and database name.
  3. Under Data connectivity mode choose DirectQuery.
  4. Pick the orders table and click Load.

Output: The table shows in the Fields pane, but no data is stored in the .pbix file. The status bar shows the storage mode as DirectQuery.

Example 2: see the query being sent

You want to know why one visual is slow.

Steps:

  1. Click View, then Performance analyzer, then Start recording.
  2. Click a slicer value, for example Pune.
  3. Expand the slow visual and look at DirectQuery time.

Output: You see how many milliseconds SQL Server took. A slow number means you should fix the database side, for example with an index, or reduce the visuals on the page.

Example 3: pick Import or DirectQuery

Decide for two reports.

Steps:

  1. Monthly sales report, data changes once a day: choose Import. It is fast and supports all DAX.
  2. Live delivery screen that must show the last few minutes: choose DirectQuery.
  3. A table that is too big to copy into the file: choose DirectQuery.

Output: Each report uses the mode that fits how fresh and how big the data is.

See also: Connect MySQL to Power BI · On-premises data gateway · Import vs DirectQuery guide

Frequently asked questions

How does DirectQuery work in Power BI?

Power BI stores no copy of the data. Each click on a visual sends a query to the database and shows the result it gets back.

Is DirectQuery faster than Import?

Usually no. Import is faster because the data is already in memory. DirectQuery gives fresher data but each visual waits for the database.

Can I switch from DirectQuery to Import?

Yes. In Model view select the table and change Storage mode to Import in the Properties pane. This switch cannot be undone for that table.

Ravi Sir's Cards for this lesson

DirectQuery in practice. Some pages must show today's orders right now. DirectQuery can do that, but it has a cost.Revision card

मराठी cards