Power BI Performance Optimization

A slow report frustrates readers and discourages use. Most speed problems trace back to a few common causes, and each cause has a clear fix. Performance tuning follows a simple pattern: measure, find the bottleneck, and fix one thing at a time.

The Traffic Analogy

A city commute slows down when too many cars use one narrow road. Widening the road, removing unneeded trips, and choosing smarter routes all help. A report slows down for similar reasons: too much data, too many visuals, and heavy formulas share one pipeline.

Slow report                       Fast report
Wide tables  ====\                Narrow tables ===>
Many visuals ====> one road       Few visuals   ===> smooth flow
Heavy DAX    ====/                Light DAX     ===>

Step 1: Measure with Performance Analyzer

1. Open the View tab and select Performance analyzer.
2. Click Start recording.
3. Click Refresh visuals, or interact with slicers on the page.
4. Read the list of visuals with timing for each.

The analyzer splits time into three parts. DAX query time measures the formula work. Visual display time measures drawing. Other time measures waiting on background tasks. Fix the slowest visual first.

Step 2: Trim the Data Model

Remove Unused Columns

Every column takes memory. Delete columns that no visual, measure, or relationship uses.

Reduce Rows

Filter old history in Power Query when readers never look at it. Fewer rows mean a smaller model and faster refresh.

Avoid High-Cardinality Columns

A column with millions of unique values, such as a time stamp down to the second, compresses poorly. Split a date and time into two columns, or round the time to the minute.

Turn Off Auto Date and Time

This setting creates hidden date tables for every date column. Disable it in Options and use one shared date table.

Step 3: Build a Star Schema

Narrow fact tables linked to dimension tables compress better and filter faster than one huge flat table. Relationships with one-to-many cardinality and single direction filtering run efficiently.

Step 4: Write Efficient DAX

• Use variables so the engine calculates repeated expressions once.
• Prefer measures over calculated columns for aggregations.
• Use DIVIDE in place of the slash operator to handle zeros smoothly.
• Filter on columns inside CALCULATE instead of whole tables.
• Avoid iterating over millions of rows when a simple aggregation can answer the question.

Step 5: Simplify the Report Page

Problem	Fix
Twenty visuals on one page	Keep to eight or fewer and split into pages
Large tables with thousands of rows	Apply Top N filters or summarize
Many slicers set to dropdown lists of huge columns	Use fewer slicers and smaller lists
Heavy custom visuals	Swap in built-in visuals where possible

Step 6: Check Query Folding

Query folding pushes Power Query steps back to the source database so the database does the work. Right-click a step in Power Query Editor and look at View native query. When the option is available, folding works for that step. Place foldable steps such as filters and column removal early in the sequence.

Worked Example: A Ten-Second Page

A sales page takes ten seconds to load. Performance Analyzer shows one matrix consuming seven seconds in DAX query time. The matrix calls a measure that loops over every row of a fifteen-million-row table. The author rewrites the measure with a simple SUM inside CALCULATE and removes a time stamp column. The page now loads in under two seconds.

Before: Matrix 7.0s | Cards 1.2s | Chart 1.8s  = 10.0s
After:  Matrix 0.8s | Cards 0.5s | Chart 0.7s  =  2.0s

Key Points

• Performance Analyzer reveals which visual and which query step slows a page.
• Smaller models run faster, so remove unused columns and old rows.
• Star schemas and simple relationships speed up filtering.
• Variables and focused filters make DAX lighter.
• Fewer visuals per page cut load time.