How an Asset Management Firm Cut Power BI Dashboard Load Times From 52 Seconds to 7 Seconds?
Data Model Optimization · DAX Refactoring · Power BI Capacity Tuning
At a Glance
| Industry | Financial Services (Asset Management) |
| Services | Power BI Performance Optimization · Data Modeling · DAX Development |
| Challenge | Dashboards taking 52+ seconds to load across hundreds of reports — pushing portfolio managers and analysts back to spreadsheets |
| Solution | Data model restructuring, DAX refactoring, DirectQuery optimization, and Power BI capacity tuning |
| Key Result | Average load time cut from 52 seconds to 7 seconds — 85% faster · Full recovery in user adoption |
About the Client
The client is an asset management company responsible for handling investment portfolios of various asset classes for both institutional and private investors. Within two years, the Power BI environment of the company evolved from using a few internal management reports to having dozens of dashboards used regularly by portfolio managers, analysts, risk teams, and senior executives.
However, rapid growth came at a price: lack of performance management eventually caught up with the firm. As it happens, when we started working with them, the loading time for some key dashboards was close to 50 seconds. Naturally, users were not willing to wait that much – they went back to Excel.
The Problem
Problems with the Power BI performance typically arise from multiple factors. In this case, when the firm reached out to Aegis for help, its Power BI environment already featured several different factors, which degraded the environment's performance individually but worked together, causing slow-loading reports and dashboard visuals.
First of all, there were no data modeling best practices used in the firm. For instance, some tables were filled with columns unused in the company's reporting; thus, they kept eating memory space while performing refreshes and queries. At the same time, there was no approach to aggregating high-cardinality data such as free-text columns and timestamps featuring millisecond precision.
Secondly, the DAX code was not optimized and did not follow good practices. Various people had been writing code in a chaotic way, adding new measures without following a certain logic. This resulted in context transition errors, inefficient time intelligence functions, and row-by-row calculations. Moreover, some measures call others in a chain, causing unnecessary calculations of the same measures many times.
Thirdly, the use of DirectQuery did not pay off due to wrong transformations. Several reports used DirectQuery mode and connected to a company's data warehouse. However, the Power Query transformations were not folding properly, leaving the processing in the warehouse to be done in Power Query's in-memory processing engine. This caused significant performance issues when several queries were executed simultaneously.
Finally, there were problems with the configuration of the capacity. Specifically, background refresh operations conflicted with interactive queries executed in the morning, just when portfolio managers accessed their dashboards before entering the trading period. The result was 94% utilization of the capacity within the timeframe between 8:30 and 10:00 am.
There was no performance monitoring in place. The problems had been visible to users for months before anyone had a systematic view of what was causing them or where to start.
What We Did?
Aegis Softtech ran a full performance audit first, then executed a structured optimization programme across four layers: the data model, DAX, query folding, and capacity configuration.
Start with the audit
Optimization can’t be done blind. The engagement began with a comprehensive audit that involved DAX Studio, Performance Analyzer, and VertiPaq Analyzer being run on the company’s top twenty reports. The outcome of the audit is an ordered list of issues based on their real-time impact on load time – so optimization was applied to the right issues, rather than the easiest.
What the audit found:
- 34% of the total model memory was consumed by columns that appeared in zero reports
- The five slowest measures accounted for 68% of the total query time across the most-used dashboards
- Three DirectQuery reports were generating full table scans due to failed query folding
- Peak capacity utilization was hitting 94% every morning between 8:30 and 10:00 AM
Data Model Restructuring
With VertiPaq Analyzer data as the guide, the semantic models were rebuilt properly:
1.
140+ unused columns removed from fact and dimension tables, reducing model size by 38% immediately.
2.
High-cardinality columns were replaced; free-text fields and millisecond-precision timestamps were swapped for lower-cardinality equivalents that actually matched the reporting use cases.
3.
Data types corrected: integer and boolean columns that had been stored as text were fixed, reducing memory consumption and improving scan speed.
4.
Aggregation tables were built for the three largest fact tables: daily position data and transaction history. Summary-level dashboard queries now hit the aggregation table instead of scanning full grain data.
DAX Refactoring
All measures were examined and revised as necessary in each of the five most impactful reports:
- Unnecessary context transitions were eliminated by restructuring CALCULATE patterns in iterating measures
- Repeated sub-expressions were replaced with variables, cutting redundant recalculation
- Non-standard time intelligence patterns were replaced with optimized DATESINPERIOD and SAMEPERIODLASTYEAR patterns using the certified date table
- Deep measure chains were collapsed to reduce evaluation tree depth
DirectQuery and Query Folding
The three DirectQuery problem reports were all resolved by changing the Power Query steps to folding operations to the SQL Server database. If not needed for the particular use case, we migrated to Import mode with incremental refresh, thus removing any dependency on the warehouse for queries.
Capacity Tuning
Two capacity settings made a very large difference: Background refresh operations were rescheduled to run between 2:00 and 6:00 AM. Completely isolated from the 8:30–10:00 AM interactive peak. Query memory limits were adjusted to prevent individual runaway queries from monopolizing shared capacity.
Utilization fell from 94% to 51%.
How We Delivered It?
Performance Audit
Comprehensive diagnostics for all 20 most-used reports. Priority report list based on findings with quantitative impact measurements before any optimization was started.
Quick Wins
Column pruning, data types adjustment and scheduling capacity management changes that do not require architectural changes. In this step alone, the most popular dashboard was reduced from 52 seconds to 23 seconds.
Measures Refactoring
A comprehensive review of all measures in the top five most impactful reports and subsequent refactoring. Measure documentation provided to the internal analytics team.
Table Aggregations
Creation of an aggregation table for the three biggest fact tables. Reduction of cardinality and correction of the Star Schema. Import mode implementation and DirectQuery performance tuning.
Validation and Handover
Validation of all optimized reports end-to-end load times. Monitoring dashboard created for the internal team. Optimization best practices were compiled for future report development. Transfer of knowledge to the firm's Power BI development team.
The Results
| Metric | Before | After |
|---|---|---|
| Average dashboard load time (top 20 reports) | 52 seconds | 7 seconds |
| Model memory consumption | Baseline | 38% lower |
| Peak capacity utilization (8:30–10:00 AM) | 94% | 51% |
| Unused columns in semantic models | Baseline | 140 removed |
| User adoption (reports open per day) | Declining for 4 months | Recovered to pre-deterioration levels |
| DirectQuery reports with folding failures | 3 | 0 |
What Made the Difference?
Measure before you optimize.
Measure before optimizing. Without DAX Studio and VertiPaq Analyzer, efforts will be wasted addressing the wrong issues. Five measures made up 68% of the total query cost, and determining that fact on the first day determined our overall optimization approach.
Models accumulate debt quietly.
One hundred forty columns were unused, and three connections using DirectQuery were failing to update properly for months, resulting in a gradual decline in performance without setting off an alert of any kind. Regular performance auditing finds those kinds of issues before users become aware.
Capacity tuning is not optional.
Doing a data refresh in the mornings while users were performing interactive queries was one of the biggest mistakes we fixed in the process. It's difficult to make such drastic improvements without changing workload timing.
Performance is an adoption problem.
A four-month trend in declining usage of reports was halted by the optimization process. Users do not leave products based on strategy or preference; they leave because it's too slow.
Technology Stack
- Power BI (Semantic Models, Power BI Service, Premium Capacity)
- DAX Studio (query profiling and measure optimization)
- VertiPaq Analyzer (model memory and cardinality analysis)
- Performance Analyzer (visual-level query timing)
- Power Query (query folding analysis and optimization)
- Power BI Premium Capacity (workload configuration and tuning)
We're also well-versed in .NET, .NET Core, Microsoft Fabric, and Azure Synapse. Take a look to see how these capabilities round out your full ecosystem.
Is Your Power BI Environment Slowing Down?
If dashboards are taking more than a few seconds to load, the root cause is almost always in the data model, DAX layer, or capacity configuration — not the reports themselves. Aegis Softtech provides targeted performance optimization engagements that deliver measurable, documented improvements.
Book a Power BI Performance Audit
*Client identity is confidential. Project details verified through internal delivery records. Reference available on request.*