For most small and mid-sized businesses, Import is the right choice. It gives you the fastest reports and lets you use every Power BI feature, and Microsoft recommends it as the default. Choose DirectQuery only when you truly need data that's more current than your refresh schedule allows, when the data is too big to import, or when a rule says the data can't be copied out of the source system. If you need a bit of both, a composite model lets you import most tables and leave one or two in DirectQuery.

What each one does

Import copies your data into Power BI and stores it, compressed, in memory. Reports query that copy, which is why they're fast. The catch is that the copy is only as fresh as your last refresh.

DirectQuery leaves the data where it is. Every time someone opens a page, clicks a slicer or changes a filter, Power BI sends a query back to the source database and waits for the answer. The data is always current, but every click now depends on how fast your database is.

Why Import is usually the better choice

Import models are faster, simpler and more capable. With Import you get:

  • Speed. Visuals query compressed data in memory instead of waiting on a remote database.
  • Full Power Query. You can reshape data any way you like. In DirectQuery, your transformations have to translate into a single query the source understands, and many don't.
  • Full DAX. DirectQuery restricts some modeling features. For example, calculated columns can only use simple row-level logic, and the automatic date hierarchy isn't available.
  • No load on your source system. A busy report in DirectQuery can send a lot of queries to a database that's also running your business.

The main cost of Import is freshness. On a Power BI Pro license a model can refresh up to 8 times a day. Premium Per User and Premium capacities allow up to 48. For most small businesses, a refresh every few hours during the working day is plenty.

When DirectQuery makes sense

We'd look at DirectQuery when one of these is true:

  • You need data that's minutes old, not hours. A warehouse screen or a support queue where people act on what they see right now, and 8 refreshes a day isn't enough.
  • The data is too big to import. Microsoft caps a Pro model at 1 GB. Sometimes a table is simply too big even after you've trimmed it.
  • Security has to stay at the source. Some organizations need the database's own permissions to decide who sees what, on every query.
  • You're not allowed to keep a copy. Some contracts or regulations say the data can't be stored outside the source system.

"We want it to be real-time" on its own isn't a strong reason. Ask what people will do differently with data that's five minutes old instead of two hours old. If the answer is "nothing," use Import.

What DirectQuery costs you

Know these limits before you commit:

  • Slower reports. Microsoft's guidance is that visuals should refresh in under 5 seconds, and that anything over 30 seconds makes a report hard to use. With DirectQuery, speed depends entirely on your database.
  • A 1 million row limit. A query that returns more than 1,000,000 rows to Power BI fails. Detailed table visuals hit this more often than you'd think.
  • Query timeouts. The Power BI service cancels DirectQuery queries that run for more than about four minutes.
  • Numbers that don't line up. Each visual sends its own query at a slightly different moment, so on a fast-changing table two visuals can show slightly different totals.
  • More load on your database. Every user, every click, every visual is a query. Pro also limits each data source to 10 active connections at a time.
  • Harder troubleshooting. When a report is slow you have to work out whether it's the model, the DAX, the network, the gateway or the database.

The middle options

You rarely have to pick one for the whole model.

  • Composite models. Import most tables and put only the big or fast-changing one in DirectQuery. You can set this per table in Power BI Desktop's model view.
  • Dual storage mode. Set shared lookup tables (customers, products, dates) to Dual, so Power BI uses the imported copy when it can and queries the source only when it has to.
  • Incremental refresh. Keep Import, but only refresh recent data. This makes frequent refreshes on large tables practical and works on Pro.
  • Real-time partitions. Incremental refresh can add a DirectQuery partition for the newest data, with older data imported. This needs Premium Per User, a Premium or Fabric capacity, or Embedded; it isn't available on Pro.
  • Direct Lake. If your data already lives in Microsoft Fabric, Direct Lake gives you close to Import speed without scheduled refreshes.

One thing to know before you start: you can change individual tables between Import, DirectQuery and Dual, but you can't flip a whole Import model to DirectQuery with one setting. Decide early, or plan on building a composite model.

How we'd decide for a small business

  1. Start with Import.
  2. Trim the model (see our post on memory limit errors for how) and set a refresh schedule that matches how people work.
  3. If one table truly needs fresher data, move only that table to DirectQuery and set its lookup tables to Dual.
  4. Go fully DirectQuery only if the data can't be copied or can't fit.

If your reports are already slow, our guide to why Power BI reports load slowly explains how to tell whether DirectQuery is the cause.

When to call someone

This is a decision that's cheap to get right at the start and expensive to change later. It's worth a second opinion if you're about to build a model on a large or busy database, if a DirectQuery report is timing out, or if you're weighing Premium Per User against a Fabric capacity to get more refreshes.

If something is broken right now, a $299 Power BI fix covers one problem, fixed by a senior consultant, and you don't pay if we can't fix it. For a model design or rebuild, see Power BI advisory.

Sources: Microsoft Learn, DirectQuery in Power BI, Use DirectQuery in Power BI Desktop, Incremental refresh overview.