Power BI DirectQuery Optimization - Using the Optimize Ribbon to Stop Wasting Queries
DirectQuery in Power BI is one of those features that sounds brilliant on paper and then punishes you in practice. You get real-time data without importing anything. No scheduled refreshes, no data duplication. The trade-off? Every single interaction in your report fires a query to the source database. Change a field? Query. Add a measure? Query. Resize a visual? Believe it or not, sometimes that's a query too.
For reports connected to large SQL databases or data warehouses, this means report authoring can be painfully slow. I've sat with clients who wait 30 seconds every time they drag a column into a visual. Multiply that by the hundreds of small changes you make while building a report, and you're looking at hours of dead time.
Microsoft's Optimize ribbon changes this, and it's one of the most practically useful additions to Power BI Desktop in recent memory.
The Core Idea - Pause Visuals
The Optimize ribbon gives you a button called Pause Visuals. When you click it, Power BI stops sending queries to your data source as you make changes. Visuals enter a "pending" state - they still show what they looked like before, but they're not actively querying.
This sounds simple. It is simple. But the impact is significant.
Consider a typical scenario. You're building a report with five visuals on a page. You want to add two new measures and assign them to tooltips on a couple of visuals. Without Pause Visuals, every step fires queries. Create a measure - five visuals refresh (five queries). Add that measure to a tooltip - another query. Create a second measure - five more queries. Add it to another tooltip - one more query. That's twelve queries for what is, conceptually, a single task.
With Pause Visuals active, you do all of that work, then click Refresh once. Two queries. Done.
When This Actually Matters
For import mode reports where data lives in memory, none of this matters much. Queries resolve in milliseconds. DirectQuery is where the pain lives, and it's also where a growing number of Australian organisations are heading.
Why? Because regulatory requirements, data volume, and real-time needs are pushing more businesses toward DirectQuery or composite models. Financial services firms that need up-to-the-minute data. Manufacturing companies with IoT sensors streaming into Azure SQL. Retailers with large transaction databases they don't want to duplicate.
If your organisation uses DirectQuery and your report authors are complaining about slow development, the Optimize ribbon is where to start.
Practical Scenarios
Building New Visuals
Say you're adding a matrix visual from scratch. You need to add three or four fields, set up a filter, and maybe adjust the formatting. Without Pause Visuals, you send a query for every field you add - that's at least four queries before you've even filtered the data. And those early queries are unfiltered, meaning they're pulling the largest possible dataset.
With Pause Visuals, you add all your fields, set your filter, then refresh once. The single query that fires is already filtered, so it returns faster and puts less load on your data source.
This is not a minor optimisation. On one client project we worked on - a logistics company with a 200GB+ SQL database behind DirectQuery - the difference between authoring with and without Pause Visuals was roughly 4x faster development time.
Creating DAX Measures
This is where the query multiplication gets ugly. In a DirectQuery report, creating a new DAX measure triggers a refresh of every visual on the page. If you're writing three or four measures in a session (which is normal), you're looking at 15-20 unnecessary queries just from measure creation.
Pause Visuals stops all of that. Write your measures, add them to your visuals, then resume. The total query count drops dramatically.
Editing Relationships in Model View
The Optimize ribbon also affects how you work in Model View. Previously, every time you changed a relationship between DirectQuery tables, Power BI would query the data to preview and validate. Now you can edit relationships directly in the Properties pane without triggering any queries. You make your changes, hit Apply, and validation happens once.
For complex models with many relationships, this alone saves significant time.
Query Reduction Settings
Beyond Pause Visuals, the Optimize ribbon includes Optimization Presets. The Query Reduction preset changes how visuals interact with each other. By default, Power BI uses cross-highlighting - select a bar in one chart and every other chart on the page adjusts. With DirectQuery, each of those adjustments is a query.
Query Reduction can disable cross-highlighting or add an "Apply" button to slicers so they don't fire queries until you're ready. For dashboards with heavy user interaction, these settings can reduce query volume by 60-70%.
What We Recommend
For any DirectQuery report development, here's what works:
Always start with Pause Visuals on. Make it your default. Build your visuals, add your fields, set your filters, then refresh.
Use Query Reduction presets for published reports. End users interacting with the report will generate fewer queries, which means faster response times and less load on your data source.
Batch your DAX measure creation. Write all your measures in one session with visuals paused, then refresh once.
Use the Performance Analyzer alongside the Optimize ribbon. It shows you exactly which queries are being generated and how long they take. This is where you find the real bottlenecks.
The Bigger Picture
The Optimize ribbon isn't going to fix a badly designed data model or a poorly indexed database. If your DirectQuery source is slow, it'll still be slow - you'll just fire fewer slow queries during development. The real performance work happens at the data layer.
That said, reducing unnecessary query volume during development is a genuine quality-of-life improvement. Report authors work faster. Data source administrators stop getting angry about unexplained query spikes during business hours. Everyone's happier.
If your organisation is using DirectQuery and you haven't explored the Optimize ribbon yet, it's worth an afternoon to learn. Our Power BI consultants regularly set this up as part of report development standards for clients, alongside proper data modelling and business intelligence practices.
For teams that are hitting the limits of what DirectQuery can do, the conversation usually moves toward composite models or dedicated analytics platforms - which is a bigger discussion, but one we're happy to help with through our Microsoft Fabric consulting engagements.