For advanced practitioners, Power BI performance optimization is the key to delivering a fast, smooth analytical experience at enterprise scale. Over the past four articles, we’ve explored techniques to identify bottlenecks using the Power BI Performance Analyzer, speed up data models, DAX calculations, and report visuals. This capstone guide brings all those insights together into one practical resource. We’ll start with a checklist of best practices summarizing the series, then explore advanced techniques (like hybrid tables, gateway tuning, and Premium capacity considerations) that can further accelerate your solutions. To illustrate the real-world impact, we’ll share a real-world B EYE client success story where these optimizations achieved a 10× improvement in report load times and data processing speed. Finally, we’ll link back to deeper-dive posts throughout, so you can explore any topic in more detail.
Explore B EYE’s Power BI Services
Power BI Performance Optimization Best Practices Checklist
Use the following numbered checklist to review and optimize your Power BI solutions. These best practices combine key takeaways from our previous four articles on data modeling, DAX, visualization, and refresh optimizations:
1. Streamline the Data Model
Keep your model lean and efficient. Remove any unnecessary columns and rows that aren’t needed for analysis. Using a star schema design (fact and dimension tables) is highly recommended – it’s a best practice for both performance and accuracy. Avoid overly complex snowflake models or giant single tables; a well-designed star schema improves query speed and ensures correct results. Also, optimize data types (e.g. use numeric instead of text where possible) to help the VertiPaq engine compress data more effectively. Every reduction in model size can boost performance. In addition, it’s good practice to put a line for deciding the data granularity on project planning stage, so this can make logical translation to the next point with storage and aggregation.
2. Use Proper Storage Modes & Aggregations
Choose the right storage mode for each table based on data size and usage. Import mode gives the fastest queries, but large tables can bloat memory. For massive fact tables, consider DirectQuery or Dual mode for detail-level data, while keeping summary data in Import mode (Composite model). This lets you offload huge fact data to the source while caching key aggregates in memory. You can also leverage user-defined aggregations – pre-summarize or aggregate data at a higher grain to reduce query time at runtime. The trade-off is losing detail, so find a balance (or use drill-through to DirectQuery for details on demand). Hybrid tables (covered later) are an advanced version of this principle, combining Import and DirectQuery in one table for an optimal mix of performance and freshness. Direct Lake functions as a storage mode variant for Power BI semantic model tables housed within a Microsoft Fabric workspace. Its design is optimized to facilitate rapid in-memory loading of substantial data volumes sourced from Delta tables. These Delta tables, in turn, archive their data as Parquet files within OneLake—the unified storage repository for all analytics data. Once data populates memory, the semantic model empowers high-performance querying.
3. Limit Calculated Columns; Prefer Power Query
Avoid using too many DAX calculated columns in your model. Calculated columns are stored in memory and increase model size and refresh time. Instead, create those extra columns in Power Query (M) or in the source database whenever possible. Power Query transformations can often be folded to the source (so they don’t slow down refresh) and result in smaller, more efficient models. Similarly, disable “Auto Date/Time” in Power BI (which creates hidden date tables for every date field) unless you truly need it – this feature can add many unnecessary tables and bloat your model. Use centralized Calendar dim table instead. You can have multiple connections (all except one of them inactive) to it, which can be switched in DAX measures with USERELATIONSHIP() function. The leaner the model, the faster your refreshes and queries. So, check your queries if they are folding correctly inside the PQ. After all, data should be transformed as far upstream as possible, and as far downstream as necessary.
4. Optimize DAX Measures
Review your DAX measures for any performance red flags. Avoid heavy iterators (the X functions like SUMX, FILTERX) in favor of simpler aggregations when possible. For example, use SUM( Sales[Amount] ) instead of SUMX( Sales, Sales[Amount] ) when you don’t need row-by-row logic. Prefer measures over calculated columns for calculations that can happen on the fly – measures only compute when needed and don’t bloat your model size. Also, use variables to store intermediate results in measures. Variables prevent repetitive calculations and allow the engine to reuse results, which can drastically improve performance for complex measures. In short, write DAX that lets the VertiPaq engine do bulk calculations (avoid cell-by-cell loops), and reuse logic through measures/variables rather than duplicating expressions. Just as a consideration to the using variables, please note that they hold only the evaluated result of the calculation they are assigned to. That means that you cannot use Calculate to change the filter context of stored value.
5. Simplify Visuals and Reduce Visual Count
Each visual on a Power BI report page introduces a query and rendering overhead. Keeping your report pages minimal and focused will improve load times. Don’t get offboard with conditional formatting. Тoo much conditional formatting could also cause the visuals to take more time. Avoid cluttered dashboards with dozens of visuals; instead, only show visuals that deliver insight. In fact, reducing the number of visuals (even non-data elements like shapes and textboxes) can have a huge impact on load time. One Power BI report page went from 27 seconds down to 10 seconds just by removing or consolidating excess visuals – no data or DAX changes at all! The author achieved this by using fewer visuals to display the same information and cutting out decorative shapes/textboxes. The bottom line: minimize visuals per page and prefer simpler visuals over overly custom or complex ones. If you need lots of design elements, consider combining them (e.g. using a background image instead of many shape objects) so that Power BI has fewer items to render.
6. Optimize Slicers, Filters, and Interactions
Slicers and filters are necessary for interactivity, but too many can slow a report. Follow slicer best practices: use hierarchy slicers or drop-downs instead of dozens of individual slicers, and avoid cascading slicers that filter each other (each change can trigger multiple queries). Where possible, use report/page filters (in the filter pane) instead of many visible slicers – the filter pane is more performance-efficient. Limit the number of slicers on a page to only what users truly need. Always check if there is a hidden slicer left that is not supposed to be there if you use sync slicers menu. Additionally, check your visual interactions (Edit Interactions settings): if every slicer filters every visual, a single selection can spawn a lot of queries. Turning off interactions for visuals that don’t need to respond can reduce unnecessary load. Finally, prefer basic filters over advanced filtering conditions if applicable, and avoid using visual-level filters to hide data (better to do it in the DAX or query). In summary, simplify your filtering approach to cut down on query chatter.
7. Enable Query Folding and Source Pushdown
When using Power Query transformations, always try to achieve query folding – meaning your filters and transformations are pushed back to the data source (database) to execute there. Folding dramatically speeds up loading because the source returns only the needed data. To maximize folding: perform filtering and column removals as early as possible in your Power Query steps. For example, remove unwanted columns and apply date filters in the first steps, so they can fold. Avoid transformations that break folding (like complex custom functions or certain M operations) unless absolutely necessary. If you find that some logic cannot fold, consider handling that in the source (e.g. create a SQL view or use a native SQL query) so that heavy lifting isn’t done on the Power BI side. Utilize Power Query’s diagnostics or Query Plan to verify which steps fold. A folded query means faster refresh and less data to handle in Power BI.
8. Use Incremental Refresh for Large Datasets
For fact tables with large history, implement Incremental Refresh (available in Power BI Pro and Premium) instead of always doing full refreshes. Incremental refresh partitions your data by date and only refreshes recent partitions (e.g. the last 1 month of data) while keeping older data intact. This can dramatically reduce refresh times, since you’re only processing new/changed data. For example, if you have 5 years of data, a full refresh reloads all 5 years, but incremental refresh might only load the last few days or weeks. Less data processed = faster refresh and lower load on the source. It’s straightforward to set up with parameters for RangeStart/RangeEnd and defining policy in Power BI. those parameters should be named exactly this way in order for the Incremental refresh policy to kick in, as they are case sensitive. If you’re on Premium, you can even combine this with real-time hybrid partitions (more on that later) for up-to-the-minute data without full refreshes. Bottom line: avoid full dataset refreshes on large models – partition your data so only the new bits are refreshed.
9. Enable Parallel Loading of Tables
By default, Power BI Desktop will try to load multiple tables in parallel during refresh. Verify that Parallel loading is enabled (in Options > Data Load for your file) so that you’re fully utilizing available threads. Parallelism means if you have, say, 10 tables, it might refresh 6 at a time concurrently instead of one-by-one (the default max in Power BI Service for shared capacity is 6 parallel queries). In Power BI Premium, you can even increase this limit beyond 6. The benefit is shorter overall refresh duration. However, be mindful of source load: if all your tables hit the same database simultaneously, that database must handle the concurrent queries. If that becomes an issue, you can stagger some table loads or upgrade the source. But in most cases, parallel loading is a net win for refresh speed. (Note: In the Power BI Service, parallelism is automatically handled; just design your dataset with multiple tables/partitions so it can take advantage of it.)
10. Monitor Performance and Iterate
Last but not least, use Power BI’s Performance Analyzer and other monitoring tools to find bottlenecks. Performance Analyzer (in Power BI Desktop) breaks down how long each visual’s DAX query and rendering took. This helps you identify which measure or visual is slowing things down. Perhaps a particular visual takes 5 seconds while others are milliseconds – focus your optimization there. For more advanced analysis, tools like DAX Studio can capture detailed query metrics, and SQL Server Profiler or Fabric metrics can show refresh and query durations. In the Power BI service, leverage the built-in Metrics (if on Premium) or usage metrics reports to see slow dashboards. The key is to quantify improvements – every change you make, test the before/after in Performance Analyzer to ensure it helped. Over time, an iterative approach will fine-tune your reports to be as fast as possible. (We covered many of these techniques in our earlier articles – see those posts for deep dives into using these tools.)
By following this checklist, you’ll address the most common performance pitfalls in Power BI: inefficient data models, slow DAX, heavy visuals, and expensive refresh operations. Next, we’ll explore some advanced techniques that go beyond the basics, helping you squeeze even more performance for large-scale, enterprise scenarios.
Advanced Techniques for Power BI Performance Optimization
Even after applying the standard best practices, there are further advanced techniques that can take your Power BI performance to the next level. These are especially relevant for enterprise deployments with large data volumes, complex infrastructure (like on-prem data sources), or premium capacities. Let’s look at each technique, with practical examples of when to use them and how they impact performance.
Hybrid Tables for Fast and Fresh Data
What are Hybrid Tables?
Hybrid tables allow a single table in Power BI to have some partitions in Import mode and others in DirectQuery mode. In practice, this is usually implemented via incremental refresh with an added real-time DirectQuery partition for recent data. The result is that most of your data (historical partitions) is cached in-memory for blazing fast query performance, while the latest changes can be fetched on the fly via DirectQuery so reports are always up-to-date. Power BI presents it as one seamless table to the report author, but under the hood it’s a hybrid of import and direct query storage.
When to Use Them
Hybrid tables are ideal when you have very large fact tables that get new data frequently. For example, imagine a sales transactions table with 5 years of data where new sales come in every hour. With a normal Import, you’d have to refresh frequently to get the latest sales, which is slow and resource-intensive. With a normal DirectQuery, you’d have up-to-the-minute data but every query on historical data would be slow. A hybrid table lets you have the best of both – e.g., store the 5 years of history in memory (lightning fast), but have the last one day or last few hours as DirectQuery so you always see recent sales without a full refresh. This way, reports get “blazing fast” performance on historical data and still include the latest updates from the source.
Performance Impact
By using hybrid tables, you drastically reduce how often you need to fully refresh data. Most data is static and cached, and only the small recent slice is queried live. Microsoft noted that this leads to better resource utilization – fewer heavy refresh operations means your Premium capacity (or Pro service) can spend more cycles answering user queries smoothly. Queries that hit only the import partitions are as fast as any in-memory query. Queries that need the recent data will incur a DirectQuery call, but because that partition is kept small (say only today’s data), those calls are relatively light and quick. In our experience, hybrid tables can dramatically improve perceived report performance in scenarios with high data churn – users get fast summaries and up-to-date details without waiting on long refreshes.
Hybrid Tables Real-World Example
A retail chain has a 200 million row sales fact table. We set up incremental refresh to keep the last 7 days in a “hot” partition and the rest of the data in yearly import partitions. We enabled the “Get the latest data in real time” option (Premium feature) so that the last 1 day is DirectQuery. Store managers can now see today’s sales in their reports (updated to the last hour) alongside historical trends, all in one view. Most visuals hit the imported data (super fast), and only when they look specifically at today’s data does the report query the SQL database. The overall experience is that pages load in seconds, and if the manager hits the refresh button in the report, new sales from a minute ago will show up. This hybrid approach saved us from doing constant dataset refreshes and made the end users very happy with both speed and freshness.
Note: Hybrid tables require Power BI Premium (Per User or Fabric Capacity) for the real-time partition feature. If you’re on shared capacity (Pro), you can still simulate a hybrid approach by using composite models (mix of import and DirectQuery tables), but it’s more manual. In Premium, it’s a simple configuration as part of incremental refresh. As data volumes grow and real-time analytics needs increase, expect hybrid tables and Direct Lake technology to play a big role in scalable Power BI architecture.
Tuning the On-Premises Data Gateway
What Is On-Premises Data Gateway?
Many enterprise Power BI deployments connect to on-premises data sources via the On-Premises Data Gateway. The gateway is a middleware that bridges the cloud service to your on-prem SQL, Oracle, file servers, etc., so performance of your gateway can directly affect refresh and query speeds. Properly sizing and configuring your data gateway is crucial for throughput and reliability in enterprise scenarios.
On-Premises Data Gateway Key Performance Factors
- Hardware resources: Make sure the gateway is running on a machine with sufficient CPU and RAM. Microsoft recommends at least 8 CPU cores and 8 GB RAM for a start, but heavy workloads may need more. The gateway can execute multiple queries in parallel (each query runs in a container process), so more CPU allows more concurrent queries. Memory helps handle large data transfers. Also ensure a fast disk if your queries spool large results. In one case, a client’s gateway was on a small VM and was throttling their refresh; upgrading to a beefier server (16 cores, 32 GB) immediately improved throughput.
- Network and location: The gateway should have reliable, high-bandwidth, low-latency connectivity both to the Power BI Service and to the data source. Ideally, place the gateway as close as possible to the data source – for example, if your SQL database is on a server in New York, run the gateway on a server in the same data center or network. Minimizing network hops and latency between gateway and source can speed up DirectQuery and import operations. Likewise, ensure the gateway has a good internet connection to the Power BI Service (multiple Gigabit NICs if possible, no aggressive firewall/proxy throttling).
- Parallel query configuration: By default, the gateway will auto-scale how many queries it runs in parallel based on the machine’s CPU (it uses an adaptive algorithm). If you have a powerful server and heavy concurrency needs, you might consider tuning the gateway’s config for Mashup container counts. Each Power Query (mashup) uses a container; more containers allow more parallel queries. The gateway auto-configures these, but advanced admins can adjust MashupDefaultPoolContainerMaxCount and related settings in the gateway config file to allow more simultaneous queries – with caution, only after testing and ensuring adequate resources. The gateway performance monitoring tool (now available via a Power BI template app) can help identify if the gateway is a bottleneck and if queries are queuing up.
- Latest version and mode: Keep the gateway software updated to the latest version – Microsoft continuously improves performance and fixes bugs. Also, use the standard (enterprise) mode gateway for multi-user, not personal mode, in a production setting. For high availability and load balancing, you can set up a gateway cluster (multiple gateway servers) – queries will distribute and if one fails the others pick up. Many large organizations have 2-3 gateway nodes in a cluster to ensure reliability and share the load.
On-Premises Data Gateway Real-World Example
A global manufacturer using Power BI had slow refreshes for some reports that pulled data from an on-prem ERP database. We found the gateway server was running at 100% CPU during peak refresh times, causing delays. Our solution: we added a second gateway node (cluster) and moved the gateways onto machines located in the same network as the ERP database (reducing latency by ~50ms). We also staggered the refresh schedules (see next section) so not all datasets hit the gateway at once. The result was a smoother refresh process – what used to take 1 hour during peak now completed in ~20 minutes, and the gateway CPU never exceeded 70%. Users also noticed that live reports (DirectQuery dashboards) became more responsive after the gateway tune-up, because queries were not backing up in queue.
In short, treat your Power BI gateway as critical infrastructure. Size it correctly, monitor its performance (CPU, memory, network) and scale out if needed. A well-tuned gateway will ensure that the optimizations in your reports and models aren’t negated by a choke point in data delivery.
Power BI Licensing Models and Performance Considerations
Power BI is offered through multiple licensing models—Pro, Premium Per User, and Fabric Capacity—each with distinct features, limits, and performance profiles. Choosing the right option depends on your organization’s data size, refresh frequency needs, and the number of consumers who will access reports. The following overview will help you decide which licensing model is best for your scenario.
Power BI Pro
Overview: Pro is a per-user license providing core Power BI capabilities such as creating, publishing, and sharing reports.
Performance & Limits:
- Shared Capacity (no dedicated resources)
- Up to 8 refreshes per day
Ideal For: Small teams and moderate data requirements, where each user has a Pro license to view and edit content.
Premium Per User (PPU)
Overview: PPU is also a per-user license but unlocks most Premium features, including larger dataset support and advanced AI capabilities.
Performance & Limits:
- Up to 48 refreshes per day
Ideal For: Teams needing Premium capabilities without purchasing a full dedicated capacity. However, all viewers must have a Pro License as well.
Microsoft Fabric Capacity (formerly Premium per Capacity)
Overview: Fabric provides dedicated, organization-owned resources. It supersedes the classic “Power BI Premium per Capacity” SKUs, supporting very large datasets and heavy workloads. Fabric also offers features like Lakehouse, SQL server, Python and Spark Notebooks & pipelines and more.
Performance & Limits:
- Dedicated capacity—exclusive CPU and memory
- Very large model limits (capacity-based)
- Up to 48+ refreshes/day, scalable concurrency
Ideal For: Enterprise deployments requiring consistent performance, large-scale sharing (including to free users), and advanced administrative controls.
Note: Each option supports standard Power BI Desktop report development. More advanced Fabric capabilities (e.g., lakehouse, data engineering) are fully unlocked via a Fabric capacity.
Power BI Advanced Refresh Optimizations: Scheduling, Parallelism, and SQL Tweaks
Efficient data refresh is vital for keeping data up-to-date without bogging down the system. We’ve covered incremental refresh and parallel loading in the checklist; now let’s discuss some advanced refresh optimizations beyond those, including scheduling strategies, fine-tuning parallelism, and optimizing source queries.
Smart Scheduling of Refreshes
In an enterprise setting, when and how you schedule dataset refreshes can impact performance. Avoid piling all refreshes at the same exact time (e.g., 8:00 AM) – this can overwhelm your data sources, the network, and the Power BI service/gateway, especially if multiple large models all kick off together. Instead, stagger refresh times for different reports by a few minutes (e.g., one at 8:00, another at 8:15, 8:30, etc.) or during different hourly slots. This distributes the load more evenly. Also consider the frequency: do all reports need hourly refresh, or can some be 2-3 times a day? Often, non-operational dashboards are fine with less frequent refreshes. Use off-peak hours for the really heavy refreshes (nights or early morning) so that during the business day the capacity is focused on user queries. In Premium capacities, also be mindful of the concurrent refresh limit (which varies by SKU); staggering helps avoid hitting those limits where refreshes queue up. Finally, if you have dataflows feeding multiple datasets, schedule the dataflow first, then the datasets after, to ensure freshness without redundancy.
Maximize Parallelism (Premium Settings)
We noted earlier that Power BI by default will refresh up to 6 tables in parallel. If you have Premium, you can increase this via the Parallel loading of tables setting per dataset. For example, if you have a very high-end capacity and a dataset with 12 tables, you might set it to allow 10 in parallel (assuming your data source and capacity can handle it). Microsoft documentation shows that raising the parallelism from 6 to 9 in one test cut refresh time by ~34% for a dataset with 9 tables. This is an advanced lever – use it for large datasets on Premium, and test to see the effect. Remember there’s diminishing returns and a point of saturating either the source or the capacity CPU, so don’t just max it out arbitrarily. Also, parallelism at source: If all your tables come from one SQL database, that database might have its own limit of concurrent queries. You may need to ensure the source is capable (e.g., increase max connections or resource allocation on the source DB) to fully benefit from parallel Power BI loads.
Optimize Source Queries and Indexes
A refresh will only be as fast as the underlying source can provide the data. If you’re using complex SQL views or queries as sources, ensure they are efficient. Avoid SELECT * (pull only the columns you need). If you have filters (like only last 2 years of data), make sure those are in the query or folded so the source filters early. On the data source side, proper indexing can hugely speed up refresh. For example, if you incrementally refresh by date, the source should have an index on the date column to quickly retrieve the range you need. We’ve seen cases where adding a single index on a source table turned a 30-minute refresh query into a 2-minute operation because the database could seek instead of scan. If using dataflows or the mashup engine for heavy calculations, consider pushing those transformations to the source as well – e.g., materialize a calculated table in the SQL database that Power BI can just import directly. The less work Power BI has to do row-by-row at refresh, the faster it’ll go.
Partitioning and Partial Refresh (Premium)
For very large models, you can go beyond incremental refresh policies and use custom partitioning via XMLA. This is advanced, but essentially you can maintain many partitions (say one per month or year) and then refresh only specific ones on a custom schedule (via scripts or the REST API). This way, you could refresh recent partitions more often and older ones rarely. This level of control can optimize refresh times and enable parallel refresh of partitions (Power BI can refresh different partitions of a table in parallel if triggered via API). Combined with Premium features like Enhanced Refresh API, you can programmatically orchestrate refreshes in the most efficient way for your scenario (for instance, refresh 3 small partitions in parallel at 7 AM, then the last big partition at 7:30, etc.). This is usually employed in very large models that push the envelope.
Monitor and Tune Over Time
Use the refresh history and metrics to see how long refreshes are taking and if they’re failing or throttling. If a particular refresh step (like one table) is always the slowest, focus on that – maybe that table needs an incremental policy, or its SQL could be tuned. Sometimes, increasing the data source performance (upgrading the warehouse, or moving to a faster tier) might be the solution if Power BI is simply waiting on data. The goal is to make refreshes invisible to end-users (happening in off hours or in the background quickly) so that reports are ready when users need them.
Power BI Advanced Refresh Optimizations Example
A financial services client had a central dataset that took ~4 hours to refresh, starting at 1 AM and sometimes not finishing by the workday – causing occasional “data is outdated” complaints by morning. We performed a refresh optimization project: we identified that the bottleneck was a single huge fact table reload from a SQL DB, which scanned a entire 100M row table daily. We implemented incremental refresh to only pull the last 1 day of data (the historical data was static) – this alone cut refresh time to under 30 minutes. We also advised the DB admins to add an index on the “LastModifiedDate” column used for incremental fetch – after that, the incremental queries ran in seconds. We staggered a couple of other dataset refreshes that were hitting the gateway at the same time, moving one to start at 2 AM instead of 1 AM. After these changes, the central dataset consistently refreshed in ~20 minutes, well before 2 AM, and all reports were updated by the time employees logged in. This was a huge win for IT, as they no longer got paged at 5 AM about refresh failures or delays.
In summary, treat refresh performance as important as query performance. Through careful scheduling, maximizing parallelism, and ensuring your data source queries are efficient, you can make even large data loads run smoothly. This will enable you to deliver up-to-date data to the business without disruption.
Power BI Emerging Features and Future Scalability
The Power BI ecosystem is continually evolving, and new features are on the horizon (or recently released) that can further enhance performance and scalability. As an advanced Power BI user, keeping an eye on these emerging performance features can help you plan for the future.
Direct Lake (Fabric)
With Microsoft Fabric, a new storage mode called Direct Lake has been introduced. This essentially allows Power BI to load data directly from a data lake (OneLake, with Delta/Parquet files) into the VertiPaq engine on-demand, eliminating the need for scheduled imports. It’s like a hybrid of Import and DirectQuery – you get import-like query speed (in-memory caching) but without having to explicitly refresh data into the dataset, since it reads the lake files and caches them when needed. This can provide near real-time data with very low latency. For example, as soon as new data lands in the lake (perhaps via an ETL pipeline), Power BI can pick it up without a full refresh – it just does a lightweight “metadata sync” operation. Direct Lake is currently a premium feature tied to Fabric (so it requires Fabric capacity). It’s particularly exciting for big data scenarios where you have more data than can fit in memory at once, but you want to query portions of it quickly. As this feature matures, it could change how we design large-scale BI – possibly reducing dependence on scheduled refresh and making BI more real-time by default.
Further Engine Optimizations (VertiPaq & DAX)
Microsoft continually improves the Power BI engine itself. Recent and upcoming updates include things like improved cardinality handling (to make large distinct counts faster), better parallel processing of DAX queries, and optimizations in the formula engine to cache results more efficiently. For instance, there have been rumors of auto-aggregations – where Power BI might automatically create cache aggregates behind the scenes for DirectQuery models (some of this already exists in Azure Analysis Services with feature like Aggregations and in some AI-driven workloads). Additionally, the integration of Power BI into Fabric may allow it to leverage other engines (like Spark) for certain operations in the future.
AI-powered Performance Analysis
We foresee more AI assistance in identifying and resolving performance issues. Power BI already has Smart Narratives and AI visuals; it’s not a stretch to imagine a feature that analyzes your report usage and suggests optimizations (e.g., “Visual X is consistently slow, consider summarizing data or reducing points”). While not announced yet, Microsoft’s AI push could extend to the admin side – maybe a Copilot that can auto-review your dataset for potential bloat or an AI that can propose a refresh schedule based on usage patterns. Keep an eye out for announcements in this space.
Scalability with Composite Models and Data Mesh
New composite model capabilities (like DirectQuery for Power BI datasets and data model sharing) enable building a semantic layer that is more distributed. Performance-wise, this means you can have specialized models (e.g., a core certified dataset) and then thin reports or composite models that extend it, rather than duplicating large models. It’s a way of scaling the architecture for performance by reducing redundancy. Also, features like OneLake (a single storage for all data) mean that multiple tools (Power BI, Synapse, Spark) can share the same data without copies – which in turn means if the data is optimized once (say partitioned parquet in the lake), all tools benefit. This convergence might simplify the performance tuning in the long run as we won’t always have separate pipelines for BI vs data science.
Gen2 Premium and Beyond
Premium Gen2 (now the standard) removed limitations like memory eviction causing refresh failures and introduced on-demand scaling. We expect “Premium Gen3” or Fabric capacities to continue this trend – making the backend more elastic. In the future, you might not even notice capacity limits; the system could automatically allocate more resources to a workload and just charge accordingly (in fact, Fabric’s billing model is moving toward capacity by consumption). This means performance will be more in your control (via cost) – if a report needs to be very fast for a critical time, you could allocate more oomph to it.
In essence, the future of Power BI performance is faster, larger, and more automated. With hybrid and direct lake strategies, real-time big data analysis becomes feasible. With engine and AI improvements, the platform will handle more optimization for you. That said, the core principles you’ve learned (modeling, DAX, etc.) remain relevant – they ensure that whatever advances come, your solutions will be able to take full advantage. We at B EYE stay on top of these developments to help clients future-proof their Power BI deployments for performance and scalability.
Success Story: 10× Faster Reporting for a B EYE Corporate Client
To demonstrate how these best practices come together, let’s look at a B EYE’s client success story. B EYE was engaged by a large corporation to improve the performance of their Power BI reports. They were struggling with slow reports and refresh delays. By applying the techniques outlined above, we achieved a 10× improvement in both report load times and data processing speed.
The Challenge
Our client’s Sales Analytics report was notoriously slow. It took over 30 seconds to load the main dashboard page and nearly 2 hours to refresh the dataset each night. The report was critical – used by 50+ executives every morning – so the delays were causing frustration and reducing trust in the BI solution. The company’s data model was a single huge table (~50 columns, 20 million rows) pulled from a SQL database, and the report had many visuals and slicers. They were on Power BI Pro (shared capacity). They asked B EYE to help streamline and speed up the experience.
The Assessment
Our team started by reviewing the Power BI Performance Analyzer results and the dataset design. We immediately spotted several issues: The data model was not a star schema – it was a flat table with many columns (including some high-cardinality text columns). Several DAX measures used heavy iterators (FILTER and SUMX over the big table). The report page had 15 visuals (including 5 card visuals, 3 charts, 2 maps, and several slicers) all on one screen, and every slicer was set to “All” by default, hitting the entire dataset. We also found that the Power Query was doing transformations that weren’t folding (it added a custom column after pulling all data). The on-prem SQL source wasn’t indexed well for the queries being run. Essentially, it was a perfect storm of suboptimal practices resulting in slow performance.
The Solution
We tackled the problem systematically, addressing each layer:
- Data Model Optimization: We redesigned the model into a proper star schema – one fact table for sales and separate dimension tables for Product, Customer, Date, etc. This reduced duplication and cut the fact table columns by 60%. We removed columns that were not used in any report visuals or calculations (dropping 10 unused columns, which immediately slimmed the model). We also reduced cardinality by splitting out a free-text “Comments” field into a separate table (it was rarely filtered on, and keeping it in the main table was hurting compression). Following our own checklist, we disabled Auto Date/Time and created an explicit Date dimension table. The new model was not only leaner but also more intuitive for future development.
- DAX and Measures: We reviewed all measures. One major measure was doing a CALCULATE( SUMX( Sales, [Revenue] ) , filter conditions…) which we rewrote as CALCULATE( [Total Revenue], … ) using the base measure we created for Total Revenue. By using a base measure and avoiding the row iterator, we let VertiPaq handle the summation. We introduced variables in a couple of complex measures to avoid recalculating the same expression multiple times. We also eliminated a calculated column that was flagging “High Value Sale” and turned it into a measure (since it was only used in a visual filter) – this saved memory and let us take advantage of context so it computed only when needed. After DAX optimizations, our test of the worst-performing visual went from ~5.0 seconds query time to ~1.2 seconds just by these formula changes.
- Report Visuals and UI: Working with the client’s report designer, we simplified the dashboard page. We broke one page into two: a summary page with key KPIs and a detail page with drill-down visuals. This allowed us to remove 4 visuals that were mainly supporting detail. We also replaced a custom map visual with a standard map which was more performant. We followed the advice of using fewer visuals: the new summary page had 8 visuals instead of 15. Immediately, the page load felt snappier. We also turned off visual interactions that weren’t necessary – for example, a couple of charts didn’t need to respond to a slicer for “Region” since they were already filtered to a single region; turning off that interaction saved an extra query for each interaction. The client was initially hesitant to change the design, but when we demonstrated the new design side by side with the old (the same info but laid out more cleanly), they agreed it was actually clearer and it loaded in about 3-4 seconds versus 30+ seconds. A huge win on user experience.
- Query and Refresh: We tackled the 2-hour refresh. First, since we now had a Date dimension, we implemented incremental refresh on the sales fact (Premium was not available yet, but they were open to getting PPU licenses for the dataset owners to use incremental refresh on shared capacity). We set it to refresh only the last 1 month of data incrementally. We also optimized Power Query steps – we made sure filters (like only last 5 years of data) were applied before other transformations to enable query folding. We verified in Power Query diagnostics that indeed only a subset of data was being pulled. On the SQL side, we worked with their DBA to add an index on the “OrderDate” column, which made the partition query (last 30 days of sales) very fast. With these changes, the dataset refresh time dropped from ~2 hours to about 15 minutes (!). Part of this was fewer rows processed, part was the fact the new model was smaller (so less to load, compress), and part was the index speeding up the source query. We scheduled the sales dataset to refresh every night at 2 AM (off-peak) and also set up a smaller “Daily Sales” dataset (with just that day’s data) to refresh every hour for up-to-date numbers – that smaller model feeds a few real-time cards via DirectQuery. By staggering refreshes and using incremental techniques, the data was fresh and refresh overhead negligible.
- Capacity and Gateway: While still on shared capacity, the improvements above were enough to get the report within acceptable performance. However, anticipating future growth, the client decided to trial Premium Per User for the report creators, which allowed features like incremental refresh (which we used) and gave somewhat more consistent performance. Eventually, as adoption grew, they moved to a full Premium capacity – but importantly, by then the reports were already optimized, so they reaped immediate benefits on the dedicated capacity (near-instant load times). We also helped them tune their data gateway (which was running on a VM on-premises) – updating it to the latest version and enabling performance logging to monitor it. We found it was handling the load fine after our refresh optimizations (since fewer data was being pulled). But we still set them up with a second gateway node for redundancy.
The Results
After our optimizations, the main dashboard page load time went from 30 seconds to ~3 seconds on shared capacity (10× faster). The entire report is now interactive with no noticeable lag in cross-filtering. The dataset refresh went from ~2 hours to ~15 minutes (8× faster), and more importantly it’s incremental and usually only takes a few minutes for daily updates. User satisfaction soared – executives noticed the improvement immediately, commenting “reports pop up instantly now.” The BI team at the company also benefited: with a structured star schema and cleaner measures, it’s easier to maintain and extend the model for new requirements. And when they moved to Premium capacity later, the report scaled to hundreds of users without a hiccup, since it was already following best practices. This success story shows how applying the full range of Power BI performance best practices – from data modeling to DAX to visualization and refresh – can transform a sluggish report into a high-performing asset. B EYE’s expertise helped our client not only speed up their analytics, but also set a template for all their future Power BI development.
(For more detailed discussions on each optimization, check out our previous articles on data model design, DAX tuning, report optimization, and refresh best practices – this success story put all of those into action!)
One-Stop Reference: Deep Dive Articles
For readers who want to dive deeper into specific topics discussed in this guide, here are the internal links to our previous four articles that cover each area in detail:
By following the guidance in those articles and the checklist above, you’ll have all the tools needed to diagnose and turbocharge any slow Power BI report.
Power BI Performance Optimization: Next Steps
Performance tuning is an ongoing journey, but with the right techniques, even the most complex Power BI reports can achieve snappy, at-scale performance. We hope this comprehensive guide serves as a valuable reference as you tackle your own optimization projects. Remember, a well-optimized report not only makes end-users happy but also builds trust in your data and BI platform.
Have Power BI Questions?
Let’s talk!
Ask an expert at +1 888 564 1235 (for US) or +359 2 493 0393 (for Europe) or fill in our form below to tell us more about your project.