Power BI Performance Optimizer - Simply Explained

Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. Why a slow Power BI report is rarely a power BI problem? Your power BI report isn't slow because of a bug. It's slow because of a decision somebody made long before that report existed, and it's rarely the decision people blame. First, a slow report burns shared capacity. It makes people stop trusting the numbers, and it eats hours out of your team's week. Power BI ships with tools built for exactly this, and the most useful one is already sitting in your ribbon waiting for you to press record. Welcome to the M365 FM podcast, where we explore the people, ideas, and technologies shaping the future of work. By the end, you'll know the four layers where a report gets fast or slow, and the one that explains most of the slowness you're seeing. Most of us know the feeling from the other side of the screen. You open a dashboard, you move a filter, and the spinner sits there for 10s then twenty, you click again because maybe it didn't register and you've made it worse. Somewhere in the building, a refresh is grinding through the night, and by the time you arrive in the morning, the numbers still aren't ready. It's easy to assume that's a bug, or that power BI is slow by nature, it's almost never either one slowness in power BI comes from choices that were made somewhere along the way, and those choices sit at four different layers of the same system. Layer one is the data source. Wherever the numbers really live. Layer two is the model, which is how power BI sees the shape of that data. It holds the relationships and the calculations behind every number you see. Layer three is the report itself, the pages and the visuals you click on. Layer four is the environment, which means the shared computing power your whole workspace draws on and how far the data has to travel to reach it. When a report feels slow, people start at layer three because that's the part they can see. The real cause usually sits further down, and one of those four layers is behind most of the slow reports you will ever meet in your working life. By the end of this, you'll know which one what people mean by a performance optimizer. The name sounds like a product you could buy, and that's the first thing worth clearing up in power BI. A performance optimizer is a job rather than a button. It's a job somebody on your team is already doing, whether they know it or not. Think about what you'd hire someone to do. They would open your slow report and watch where the seconds land. Then they'd work out whether the problem is the query, the drawing of the chart, or the data underneath. Change one thing and measure again. That loop of measure, change and measure again is the entire job. Microsoft gives you tools for every stage of that loop, which is why the name gets attached to a toolkit instead of a single switch. Some of those tools are free and already installed. Some come with a paid capacity. All of them do the same thing in the end, which is to help you find the bottleneck and then prove you fixed it. Ownership is the part that saves you the most frustration, and some of these levers belong to whoever builds the report, which is often you. Some belong to whoever owns the model, which might be a completely different person, and some belong to whoever runs the capacity, which is frequently an admin you've never met. Knowing which lever is even yours to pull keeps you from fixing the wrong thing for a week. Picture a sales dashboard that feels slow in three different ways. The page takes ages to open, which points at the report layer. A single chart takes ages to update when you change a filter, which points at the model, and the whole thing crawls at four o'clock in the afternoon, but runs fine at eight in the morning. That points at the environment because four in the afternoon is when the entire company asks for exactly the same numbers at the very same moment. That's one dashboard with three different causes and three different owners. Which is exactly why blaming power BI never fixes anything. It's the map for the rest of this episode, and we'll walk it in order, starting with the part you can open today. The optimizer already sitting in your ribbon. If you use power BI desktop, you already have a real performance tool installed and it doesn't cost a thing. It lives on the optimize ribbon and the one to meet first is Performance Analyzer, which has been sitting there quietly the whole time. Performance analyzer is a recorder. You press start and then you use the report the way a normal person would. You click a slicer, you change a filter. You open a page. Every time a visual loads the recorder writes down how long it took and where the time went inside that load. It sorts that time into a few plain buckets. One bucket is the query, which is the time power BI spent asking the model for the numbers. Another is the visual display, which is the time spent drawing the chart on screen. A third catches everything else, like one visual waiting in line behind another one, while the rest of the page finishes its own work when a single bucket clearly dominates. You found your suspect. One thing to understand, and it's the part people get wrong. Performance analyzer tells you where the time goes. It doesn't tell you how to fix it, which is why the tool on its own has never rescued a slow report. That judgment is still yours or whoever owns the model. The ribbon also has something called pause visuals, and if you build reports, it will save you real time. Normally, every time you drag a field onto a chart, power BI runs a fresh query. Do that twenty times and you've waited on twenty queries. When you pause, the report stops asking for data, and each chart shows a note that it has pending changes. You make all your edits first, then refresh. Once. There are optimization presets too, and they amount to three moods query reduction suits reports that talk to a live database, and it turns off the automatic cross filtering that fires a new query on every click. Interactivity is the opposite, and it's the default because it keeps everything instant. For reports that hold their own copy of the data, customize opens the settings so you can pick and choose one more small trick while we're here. If a page has a pile of slices, you can add an apply All and a clear all button. People then set every filter they want and apply the lot in one click. That beats firing off a separate query for each change and waiting on every one of them. All of that is useful, and none of it is where most slowness comes from, because the ribbon only measures the problem. The recorder keeps pointing at something underneath the report, and that's where we go next. The biggest lever sits under the report. Everything you click in a report is served by the model underneath it, and a badly shaped model will defeat every attempt at tidying the report. A model, in plain terms, is power by his own view of your data. It holds the tables, the relationships between them, and the calculations that turn rows into numbers you care about, like total sales or last month's margin. Microsoft calls it a semantic model, and the name is the only fancy part. One idea makes most models fast or slow, and it comes down to the shape of the data rather than the charts sitting on top of it. Imagine your data as a single, enormous list of events every sale, every click, every invoice, all stacked in one table. On its own, that list is painful to work with because everything is jammed together. So you pull the descriptions out into their own small lists one for products, one for stores, one for dates. Now the big table holds mostly numbers and codes, and the small tables hold the explanations. That shape is called a star schema. It's the difference between a model that answers in a blink, and one that grinds away at every single click. Shape matters so much because of how much data power BI has to touch. For every click, a well shaped model finds what it needs by checking a small table first and then grabbing only the matching rows it needs from the big one. A poorly shaped model forces the engine to sweep the whole thing. Then there's the quiet budget Killer, which is the number of distinct values in a column. A column holding ten product categories costs almost nothing. A column holding a unique timestamp for every one of twenty million rows costs a great deal. The engine has to track every one of those values. You don't need to memorize the term for it. You just need to notice when a column has a huge number of different values in it, because that's where your memory is going. The other habit is filtering a table visual that tries to show everything, will drag an enormous number of rows into memory, and that data gets uncompressed and rebuilt on every refresh. The fix is to cap it, show the top ten thousand rows or the top one hundred, and the experience for the person reading barely changes while the memory use falls off a cliff. Here's where I have to be straight with you. Some of these fixes need someone who can edit the model and that might not be used. Some of them also take time and testing to get right. If you learn one thing about power BI performance, make it this. Ask what shape the model is in before you ever blame the report in front of you. Import direct query and direct Lake. Every model has to answer one question before anything else. And that question is where the data lives when somebody clicks a chart. There are three answers in power BI and they work like three delivery methods. The first is import and it behaves like stocking your own pantry. Power BI copies the data into its own file storage and reads from that copy, so every click is quick and predictable. What you pay for is a copy that has to be refreshed on a schedule, and it takes memory, which means a big model can get expensive. The second is direct query, which is closer to phoning the shop every time you need something. Power BI keeps no copy, and instead every click sends a question to the original database. Nothing is ever out of date, which matters for an operations board where the number has to be true. Right now the trade traders that the speed of your report becomes the speed of that database. And databases aren't usually built for thousands of people clicking at once. The third one is newer and it only exists in Microsoft Fabric. It's called Direct Lake, and the idea is clever. In fabric. Your data already sits in one Lake, which is Microsoft's single storage layer for all of the analytics data across the entire platform. Direct Lake reads straight from there because it uses the same fast engine that import uses. It behaves like a copy you never had to make. You just never pay the price of storing one. The refresh is the clever part. Instead of copying the data, it updates the pointers to the latest files, which can take seconds instead of hours. The limits here are real, so let's be straight about them. Direct Lake needs a fabric capacity, which is a paid subscription, and it needs your tables to be well organized. It also has guardrails that depend on the size of that capacity. So on a small one, you can hit a ceiling on rows or model size. And when it can't read a table directly, it can quietly fall back to the slower direct query path. That means the speed you were promised isn't always the speed you get. The choice usually comes down to how fresh the data has to be. A nightly sales report that nobody touches during the day is happy. An import a live operations board that has to show the current minute once direct query or direct lake. If you're already in fabric with a big lake house, direct Lake is worth testing. Just go in knowing it isn't free and it isn't automatic. Fewer visuals, tighter filters. Now we're back at the layer you can touch without asking anyone's permission. And it's the report itself. Every visual on a page is its own little worker. When the page opens, each chart sends its own query and they all queue up to be served. Ten visuals means ten queries competing for the same attention. Twenty means the page feels heavy no matter how good the model underneath it happens to be. The fix is uncomfortable for anyone who loves a packed dashboard, use fewer visuals per page and move the detail somewhere else. Power BI gives you two ways to do that. One is a drill through page, which is a separate page that opens only when someone clicks on a specific row of a specific chart. The other is a tooltip page, which appears when someone hovers over a chart. Both let you keep the depth without paying for it on every page load. Next, watch out for the visuals that show far too many rows a table with hundreds of thousands of rows is one of the most expensive things you can put on a page. All of that data has to be fetched, uncompressed and drawn. If the reader only ever looks at the top twenty rows, then the visual should only ever ask the model for the top twenty rows. That leads to the mistake almost everyone makes. At least once you build a table, you leave it unfiltered and it tries to show every single row you have at once. That works on your test data and falls over the moment real data shows up in it. The cure is to set a limit in the filter pane and set it a little higher than anyone would ever scroll. One warning about custom visuals because they look like a free upgrade. They aren't automatically fast, and a badly built one can drag down a whole page along with every other visual sitting on that page next to it. Test it with real data before you ship it. The limit here is that cutting visuals can hurt the story if you cut the wrong ones. The wrong one is usually the chart somebody opens every day. Measure before and after and let the recorder tell you whether you helped the layout. Nobody thinks about now for the layer nobody sees. And that can quietly undo everything you just fixed. This is the environment and it comes down to three things that are easy to ignore shared compute gateways and distance. Start with compute in power BI and fabric. Your workspace runs on something called a capacity, and that capacity is shared. Every report, every refresh, and every background job in it draws from the same pool of processing power. One enormous report can slow down everybody else, and your fast report can feel slow, simply because it happens to be sitting next to a heavy one. Fabric handles part of this for you. It spreads out the accounting for background work so that a single heavy job doesn't instantly blow the budget. It does that without changing how fast your jobs run. When demand stays above the limit for too long, the server starts delaying work to protect the rest. And that's the moment people start seeing timeouts. When a capacity comes under real strain, there are only three answers. You make the work lighter, you buy a bigger capacity, or you move some of the work onto a different one. Microsoft's own guidance lists exactly those three, and the third one is usually the cheapest, though it does take somebody willing to do the moving. The tool for seeing all of this is the Capacity Metrics app. It works as a dashboard about your dashboard and about everybody else's. At the same time. It shows which items are eating the compute and when. So you can find the report that's making everyone else miserable. Then there's distance, which sounds trivial and isn't. Your power BI tenant lives in a specific region, and every request routes there. If your data source sits on a server in another part of the world, then every click pays a travel tax. Your gateway is sitting somewhere else. Again, only makes it worse. Microsoft's advice is simple, which is to keep your data sources, your gateway, and your capacity as close together as you can, ideally in the same region. The awkward part about this layer is that it's usually an admins decision rather than yours. That's exactly why the case has to be made with numbers. Your job is often to measure carefully and hand over the evidence, which is far more persuasive than saying the dashboard feels slow and copilot as a first pass optimizer. Now for the newest lever, which is the one people ask about most and the one most likely to be oversold. In March of twenty twenty six, Microsoft made a striking claim. According to Microsoft, a developer with moderate power BI experience could now handle optimization work that normally takes sixteen to forty hours, with copilot doing the heavy lifting. That same work lands in under twenty minutes. Microsoft reported that model and refresh times dropped by forty five percent, and that import mode model sizes shrank by nearly eighty percent. Report Visuals, Microsoft said rendered twenty five percent faster. Those are big numbers, and here's the framing to keep attached to them. They come from Microsoft's own testing rather than an independent benchmark, and Microsoft is the company selling the tool. Treat them as a strong signal that this works, not as a promise about what you will see in your own tenant once real users start clicking. In practice, it reads, the report file spots the inefficiencies and hands you a step by step plan. That part is worth understanding because it means copilot is closer to a fast diagnostician than to an autopilot. It tells you what's wrong and what to change, and a person still has to review it and decide whether the change is right for the business. A governance layer arrived alongside it, and it matters if you work somewhere careful. Fabric IQ, which reached general availability in September of twenty twenty six, lets people ask business questions in copilot and get answers grounded in your governance. Power by models. Admins also gained a control to keep specific models out of those AI experiences. Each model carries its own setting for whether read only users can reach it through copilot. For a beginner, copilot is a useful first pass, especially for finding problems you'd never have spotted on your own. It isn't a substitute for understanding the four layers, because you still have to judge what it hands you. Judgment is the one thing it can't hand over. What to remember. And one question for you. Here's the short version. A power BI report gets fast or slow at four layers. The data source, the model, the report, and the environment. The tools to inspect them are already in your ribbon, and copilot can give you a head start on the diagnosis before you dig in yourself. If you remember one thing, remember this the model underneath the report is where most slowness is born, which is why the first question is never about the chart in front of you. It's about the shape of the data behind it. So here's what I want to know. And I read every reply. What's the slowest report in your world right now? Does it drag the moment the page loads when you click or when everybody logs on at once? Your answer tells you which layer you're standing on. If this made the topic feel less intimidating, subscribe to M365 FM to master the tools and the mindset shaping the future of work.