DirectQuery Optimization in Power BI - How the Optimize Ribbon Actually Saves You Time

If you've spent any time building Power BI reports on top of DirectQuery sources, you know the pain. Every small change - adding a column, tweaking a measure, dragging a field into the wrong spot - fires off a query to your data source. For big datasets sitting in Azure SQL or Synapse, that means waiting. And waiting. And then accidentally making another change while the first one is still running.

The Optimize ribbon in Power BI Desktop is Microsoft's answer to this problem, and having used it on several client engagements now, I can say it's one of those features that sounds small but genuinely changes how you work.

The Core Problem with DirectQuery Report Authoring

Here's what happens without the Optimize ribbon. You're building a report connected to a large data warehouse via DirectQuery. You add a measure to a visual's tooltip - query fires. You add another measure - another query. You reorder the fields - yet another query. For a page with five visuals, creating a single new measure triggers five queries simultaneously, one for each visual on the page.

We had a client with a Synapse Analytics warehouse where each DirectQuery query was taking 3-8 seconds. They had report pages with eight visuals. Every time a report author made a small change, they'd sit there for up to a minute watching spinners. Report development that should have taken a day was stretching into a week.

Pause Visuals - The Feature That Should Have Existed Years Ago

The headline feature in the Optimize ribbon is Pause Visuals. When you hit pause, your visuals stop sending queries. You can make as many changes as you want - add fields, remove fields, reorder them, apply filters - and nothing gets sent to the data source until you're ready.

Visuals that need to send a query enter a "pending" state. They still show the previous data for context, but tooltips are disabled so they don't accidentally fire queries either.

Once you've made all your changes, you have three options. You can refresh a single visual to test your changes. You can refresh all visuals on the page at once. Or you can resume normal querying, which sends queries and then keeps sending them as you continue editing.

The practical difference is real. In the scenario I described above, adding two measures to two visuals and reordering them would normally fire six queries. With Pause Visuals, you fire two. That's a third of the data source load and a fraction of the wait time.

Creating New Visuals Without the Query Storm

This is where Pause Visuals really shines. When you add a brand new visual to a page, every field you drop in sends a query. If you're building a matrix with three fields, that's three queries before you've even started filtering. And those early queries are probably the most expensive ones because they're unfiltered.

With Pause Visuals turned on, you can build the entire visual - add all your fields, reorder them, apply your filters - and then send a single, filtered query when you're done. The first query the data source sees is a targeted one, not a wide-open scan of the entire table.

I've found this particularly useful when building reports against sources with row-level security or complex partitioning. The early, unfiltered queries were sometimes the ones that caused timeouts. Starting with a filtered query avoids that entirely.

Measure Creation Without the Cascade

Here's a scenario that used to drive our consultants mad. You're building DAX measures in a DirectQuery model. Every time you finish writing a measure and hit enter, all five visuals on your page refresh. You haven't even added the measure to a visual yet - the model change alone triggers a refresh.

If you're creating a batch of related measures - say, a revenue measure, a margin measure, and a year-over-year comparison - that's fifteen queries (three measures times five visuals) before you've even started using them. With Pause Visuals, you create all three measures, add them to the visual you actually want them in, and then resume. You go from fifteen queries to maybe six.

Relationship Editing Without Query Overhead

This one is more niche but worth mentioning. Previously, editing relationships between DirectQuery tables in Model view meant queries ran every time you opened the edit dialog - to load data previews, to validate cardinality, to check direction. Now you can edit relationships directly in the Properties pane without any of that.

You select the relationship line, change your settings - table, column, cardinality, direction, referential integrity - and click Apply Changes. Queries only run when you apply, not while you're deciding what to change.

Holding Ctrl lets you select multiple relationships for bulk editing, which is handy when you're restructuring a star schema and need to change several relationships at once.

Optimization Presets - Quick Wins for Query Reduction

Beyond Pause Visuals, the Optimize ribbon includes Optimization Presets with a Query Reduction option that applies several settings at once.

It disables default cross-highlighting and cross-filtering. In a typical dashboard, clicking a data point in one visual causes every other visual on the page to react - sending queries. For DirectQuery reports, this interaction pattern can get expensive fast. The Query Reduction preset turns it off by default while preserving any custom interactions you've specifically configured using Edit Interactions.

It also adds Apply buttons to slicers. Instead of every slicer change immediately querying the data source, users make all their slicer selections first and then click Apply. This is a big deal for end users too - they're not sitting through intermediate query results while they set up their filters.

The Apply button also gets added to the Filter pane, which controls all filter selections with a single button.

If you need to fine-tune these settings, the Customize option lets you mix and match. For example, you might want the Apply buttons on multi-select slicers but instant apply on single-select ones. You can also revert everything back to full interactivity at any point.

When to Use Performance Analyser Alongside

The Optimize ribbon includes a quick launch for Performance Analyser, and I'd recommend using them together. Performance Analyser shows you exactly which visuals are using DirectQuery (look for the "Direct query" row in the results), how long each query takes, and the actual SQL being generated.

This is where you find the real problems. Sometimes a visual that looks simple is generating a terrible query because of how the measures are written or how the relationships are set up. The Optimize ribbon helps you author reports faster, but Performance Analyser helps you make the actual queries better.

Practical Recommendations

Based on what we've seen across Power BI consulting engagements, here's what I'd suggest:

Make Pause Visuals your default workflow. Get in the habit of pausing before you start editing, making your batch of changes, and then resuming. It takes about two seconds to pause and saves minutes of waiting.

Use Query Reduction presets for any report going to end users. The Apply button on slicers alone is worth it. Most users don't need real-time cross-filtering, and the reduced query load means better performance for everyone.

Don't skip Performance Analyser. The Optimize ribbon helps with authoring speed, but if your underlying queries are slow, you still need to fix them. Use Performance Analyser to find the heavy hitters and optimise your DAX or your data model.

Test on realistic data volumes. The Optimize ribbon doesn't change how queries perform - it changes when they run. If your queries are slow on production data, they'll still be slow. The benefit is that you run fewer of them during development.

Where This Fits in the Bigger Picture

For organisations running business intelligence solutions on large datasets, DirectQuery is often the only practical option. Import mode has memory and refresh limits. DirectQuery lets you hit the data live, but the trade-off has always been authoring speed.

The Optimize ribbon doesn't eliminate that trade-off, but it makes it much more manageable. Combined with proper data modelling and DAX optimisation, you can build DirectQuery reports that are both fast to develop and fast to use.

If you're struggling with Power BI performance on DirectQuery sources, or you're planning a new BI implementation and weighing up Import vs DirectQuery, get in touch with our team. We've helped plenty of Australian businesses get their Power BI deployments running properly.

For the full technical details, check out Microsoft's DirectQuery optimization scenarios documentation.