Power BI Storage Modes Explained - Import, DirectQuery and Dual Without the Confusion

A client rang me a few months back with a Power BI problem that will sound familiar to anyone running reporting over a big operational database. Their sales dashboard took forty seconds to load. Every visual fired queries straight at the production SQL Server, the DBA was filing complaints, and the report authors were adamant they could not switch to Import mode because "the executives need real-time data".

Two questions later it turned out the executives looked at the dashboard each morning over coffee. The data could have been eight hours stale and nobody would have noticed. The whole performance problem existed because someone, years earlier, had picked DirectQuery as the storage mode without anyone ever revisiting the decision.

Storage mode is one of those Power BI settings that sounds like plumbing but quietly determines your report performance, your capacity costs, and how much your DBA hates you. Microsoft documents the mechanics in the manage storage mode article, and the mechanics are the easy part. The judgement about which mode to use where is what this post is about.

The three modes in plain language

Every table in a Power BI semantic model has a storage mode. You can see it in Model view by selecting a table and looking at the Advanced section of the Properties pane. There are three options, and they describe a simple trade.

Import copies the data into Power BI's in-memory engine. Queries run against that cached copy, which makes them fast - usually the fastest experience Power BI can offer. The cost is freshness. The cached data is only as current as the last refresh, and the model consumes memory in proportion to the data you import.

DirectQuery stores nothing. Every interaction with a visual sends queries back to the source system at that moment. You get current data and a tiny model footprint, and in exchange you inherit whatever performance the source can deliver under whatever load your report users generate. If two hundred people open the dashboard at 9am Monday, your source database feels all of it.

Dual is the interesting one. A Dual table can behave as either cached or DirectQuery depending on the query, and Power BI's engine decides per query which behaviour gives the right answer efficiently. It exists for composite models, where some tables are Import and others are DirectQuery, and it solves a specific problem I will get to shortly.

There is also a fourth mode you will see on Fabric, Direct Lake, which reads data straight from OneLake delta tables and gives close to Import performance without scheduled refreshes. It is set at the model level rather than per table in the same way, and it changes the calculus if you are on Fabric capacity. Worth knowing it exists, but the per-table decisions in this post are about the classic three.

The default answer is Import

I will state my bias up front because it saves time. Import is the right storage mode for most tables in most Australian business reporting scenarios, and DirectQuery should have to argue its way in.

The reasoning is not complicated. The large majority of business reporting questions are about yesterday and earlier. Sales by month, margin by product, headcount trends, project burn. Refreshing that data a few times a day covers nearly everyone, and Import gives those users a fast, snappy report that does not load up the source system. Fast reports get used. Slow reports get screenshotted into PowerPoint once and abandoned, and I have watched that exact lifecycle play out more times than I want to count.

When clients tell me they need real-time, I ask what decision would change if the number were fifteen minutes old. Most of the time there is a long pause. The genuine real-time cases exist - a logistics operation watching live vehicle movements, a contact centre wallboard, intraday trading positions - and for those, DirectQuery or a real streaming approach is correct. But "the data must be live" is usually a reflex, not a requirement, and it is an expensive reflex.

Where DirectQuery genuinely earns its place

Having argued against it, let me be fair to DirectQuery, because there are situations where it is plainly right.

Data volume is the big one. If your fact table has a couple of billion rows, importing it is somewhere between painful and impossible, and DirectQuery against a warehouse built to handle analytical queries works well. The pattern of DirectQuery into a properly dimensioned Synapse, Databricks SQL, or Fabric warehouse is solid and we deploy it regularly.

Regulatory and sovereignty constraints come up too, more often in our government and financial services work. Some organisations have rules about where data may be persisted, and DirectQuery's leave-the-data-at-the-source behaviour sidesteps a whole approval process. Whether that reasoning survives scrutiny is a separate question, but it is a real constraint people operate under.

And genuine operational freshness, as above. Just make someone defend the requirement before you pay the performance price for it.

Composite models and the point of Dual

The real-world answer is often "both". You want your two-billion-row transaction table in DirectQuery and your tidy summary tables in Import. Composite models let you mix storage modes in one model, and this is where Dual stops being trivia and becomes important.

Picture a standard star schema. A huge fact table in DirectQuery, and a Date dimension that both the DirectQuery fact and some imported tables relate to. If that Date table is Import-only, queries that join it to the DirectQuery fact get awkward - the engine has to bridge a cached table and a remote one, which produces what Power BI calls limited relationships, and those bring performance penalties and some subtle filtering behaviour changes that will eventually surprise someone with a wrong-looking total.

Set the Date dimension to Dual and the problem dissolves. When a query only touches imported tables, the Date table acts as cached and stays fast. When a query involves the DirectQuery fact, the Date table acts as DirectQuery and the join happens at the source as one native query, with a regular relationship. Same table, right behaviour both times.

The working rule I give teams is short - any dimension shared between Import and DirectQuery fact tables should be Dual. It is the storage mode of choice for shared dimensions in composite models, and once you frame it that way the design conversations get a lot quicker.

This pairs naturally with aggregations, which are the other half of the big-data pattern. Keep an imported aggregation table for the summary-level queries that make up most dashboard traffic, let the rare detail-level drill go to DirectQuery, and use Dual dimensions to keep the joins clean at both levels. Done well, users get Import-grade speed on the dashboards and full depth on the occasional drill-through, and the source system barely notices them.

The one-way door

Now the trap, and it is a proper trap. Changing a table from DirectQuery to Import or Dual is allowed. Changing a table from Import back to DirectQuery is not. Once data has been cached, that table cannot return to a pure DirectQuery mode.

Power BI Desktop warns you, but the warning reads like every other dialog people click through. The practical consequence is that storage mode changes need to be treated like schema migrations, not like toggling a setting. I have seen a team flip a set of tables to Import to debug a performance issue, discover they could not flip back, and end up rebuilding the model from source files. An hour of caution would have saved a week.

So the sequencing rule is to start tables in DirectQuery when you are unsure, and move them toward Dual or Import as the design firms up. That direction of travel is always open. The reverse is a rebuild.

Two smaller gotchas while I am listing them. Dual tables must be able to function in DirectQuery mode, which means the Power Query transformations on them have to fold to the source - if you have stacked complex M transformations on a dimension, Dual may simply refuse until you simplify or push the logic upstream. This is often the nudge that gets transformation logic moved into the warehouse where it belonged anyway, and it is a conversation our data engineering team ends up having on most composite model projects. And watch the Refresh column in the storage mode dialog - Dual tables still need refreshing for their cached side, so they participate in your refresh schedule even though they also query live.

How to choose, quickly

When we review a model in a Power BI consulting engagement, the storage mode pass takes about an hour and follows a short script. Does the business need data fresher than the refresh schedule can provide, with a named decision that depends on it? If no, Import. If the table is too large to import or constrained from being persisted, DirectQuery, ideally fronted by an imported aggregation table. Every dimension shared across both worlds, Dual. Then check query folding on the Dual tables and confirm the source can actually carry whatever DirectQuery load remains.

That hour, applied early, prevents the forty-second dashboard. Applied late, it still helps, but you are unwinding decisions rather than making them, and the Import one-way door limits your options.

If you are heading to Fabric, add one item to the script - check whether Direct Lake covers the freshness requirement before you accept DirectQuery's costs, because for lakehouse-resident data it frequently does. The capacity and migration angles of that decision are their own topic, and one we work through in our Fabric consulting practice.

Storage mode will never be the exciting part of Power BI. But of all the settings in the product, it has one of the highest ratios of business impact to attention received. Spend the hour.

Reference: Microsoft Learn - Manage storage mode in Power BI Desktop