Performance Optimization
Why Performance Matters
Report performance directly affects whether people actually use your reports. Research by Power BI engineering teams shows that reports taking longer than 5 seconds to load see a significant drop in user engagement. Slow reports erode trust in the data, encourage workarounds (like exporting data to Excel), and ultimately undermine the value of your BI investment.
The Business Impact of Performance
| Metric | Fast Reports (< 3 seconds) | Slow Reports (> 10 seconds) |
|---|---|---|
| User adoption rate | High | Low — users abandon slow reports |
| Data-driven decisions | Frequent — users explore freely | Rare — users avoid interaction |
| Support tickets | Low | High — "the report is broken" complaints |
| Executive trust | Strong | Eroded — "I'll just use the spreadsheet" |
| Capacity costs | Efficient — queries finish quickly | Wasteful — queries consume resources longer |
| Refresh reliability | Higher — fast refresh within time windows | Lower — refresh may time out |
What Affects Performance
Performance is influenced by several factors across the entire stack:
- Data model — Table sizes, column cardinality, data types, relationships
- DAX calculations — Measure complexity, iterator usage, filter patterns
- Visuals — Number of visuals per page, visual types, cross-filtering
- Data source — Query folding, DirectQuery source performance, network latency
- Service infrastructure — Capacity size, concurrent users, refresh schedules
This chapter covers optimization strategies for each of these areas.
The Power BI Architecture
Understanding how Power BI processes queries is essential for meaningful optimization.
Storage Engine vs Formula Engine
Power BI's Analysis Services engine has two main components:
| Component | Role | Characteristics |
|---|---|---|
| Storage Engine (SE) | Retrieves data from the in-memory store | Multithreaded, fast, scans compressed columns |
| Formula Engine (FE) | Evaluates DAX expressions | Single-threaded, slower, handles complex logic |
Optimal queries maximize work done by the Storage Engine and minimize work done by the Formula Engine. The Storage Engine is highly optimized for scanning compressed columnar data, while the Formula Engine processes results row by row.
VertiPaq (In-Memory Columnar)
VertiPaq is the in-memory columnar storage engine used by Power BI's Import mode:
- Data is stored column-by-column, not row-by-row
- Each column is compressed independently using dictionary encoding and run-length encoding
- Compression is most effective for low-cardinality columns (fewer unique values)
- Queries scan only the columns needed, not entire rows
- Data is stored entirely in RAM for fast access
Key insight: The more unique values a column has (higher cardinality), the larger it is in memory and the slower it is to scan. This principle drives many optimization strategies.
How Queries Execute
When a user interacts with a report visual:
- The visual generates a DAX query based on the fields, filters, and slicers
- The Formula Engine parses the DAX and creates a query plan
- The query plan sends Storage Engine queries (xmSQL) to retrieve data
- The Storage Engine scans the relevant columns in VertiPaq and returns results
- The Formula Engine combines and processes the results
- The final result is sent to the visual for rendering
Understanding the Query Plan
A query plan consists of:
- Logical query plan — What the query needs to compute (high-level)
- Physical query plan — How the computation will be executed
- Storage Engine queries — The actual data retrieval operations sent to VertiPaq
Performance issues arise when:
- Too many Storage Engine queries are generated (measure complexity)
- The Formula Engine processes too many rows (iterator functions on large tables)
- Storage Engine queries scan high-cardinality columns (large segments)
Performance Analyzer
Performance Analyzer is a built-in tool in Power BI Desktop that helps you identify performance bottlenecks in your report.
Opening Performance Analyzer
Step 1: Open your report in Power BI Desktop.
Step 2: Navigate to the View tab on the ribbon.
Step 3: Click Performance Analyzer in the Panes group.
Step 4: The Performance Analyzer pane appears on the right side of the canvas.
Recording Performance Data
Step 1: Click Start recording in the Performance Analyzer pane.
Step 2: Interact with your report — change pages, click slicers, apply filters, or click Refresh visuals to record a full page refresh.
Step 3: Performance Analyzer captures timing data for every visual that renders.
Step 4: Click Stop when you have captured enough data.
Interpreting Results
Each visual appears in the Performance Analyzer pane with timing breakdowns:
| Metric | What It Measures | What to Look For |
|---|---|---|
| DAX query | Time to execute the DAX query against the dataset | High values indicate complex measures or large scans |
| Visual display | Time to render the visual in the browser/canvas | High values indicate complex visuals or too many data points |
| Other | Miscellaneous processing (waiting for other visuals, system tasks) | High values indicate congestion from too many visuals |
What the Numbers Mean
| Duration | Assessment | Action |
|---|---|---|
| < 100ms | Excellent | No action needed |
| 100ms – 500ms | Good | Acceptable for most scenarios |
| 500ms – 1000ms | Needs attention | Investigate DAX and model optimization |
| 1000ms – 3000ms | Poor | Optimize urgently — users will notice |
| > 3000ms | Critical | Major refactoring needed |
Identifying Bottlenecks
- Sort by duration to find the slowest visuals (click the expand arrow on each visual)
- Look for visuals where DAX query time dominates — these need DAX or model optimization
- Look for visuals where Visual display time dominates — these need visual simplification (fewer data points, simpler visual type)
- Look for visuals where Other time dominates — reduce the number of visuals on the page
Exporting Results
Step 1: After recording, click Export in the Performance Analyzer pane.
Step 2: Save the JSON file for detailed analysis.
Step 3: You can analyze this file in Power BI itself or in DAX Studio for deeper investigation.
Copying DAX Queries
Step 1: Expand a visual's entry in Performance Analyzer.
Step 2: Click Copy query next to the DAX query line.
Step 3: Paste the DAX query into DAX Studio or the DAX query view for further analysis and optimization.
Data Model Optimization
The data model is the foundation of report performance. Optimizing it has the highest impact on overall performance.
Remove Unused Columns and Tables
Every column in your model consumes memory and increases model size. Unused columns waste resources.
What to remove:
- Columns used only during data loading (staging columns)
- System columns (internal IDs, audit timestamps) that are not used in visuals or measures
- Duplicate columns across tables
- Large text columns (descriptions, comments) that are not used for filtering or display
- Tables that were imported for exploration but are not used in the final model
How to identify unused columns:
Step 1: Use Tabular Editor or DAX Studio to list all columns.
Step 2: Check column references in measures, calculated columns, and relationships.
Step 3: Check visual field usage across all report pages.
Step 4: Remove any column not referenced anywhere.
Before:
Sales table: 25 columns, 10M rows, Model size: 850 MB
After removing 10 unused columns:
Sales table: 15 columns, 10M rows, Model size: 520 MB
Reduce Cardinality
High-cardinality columns (many unique values) compress poorly and increase model size. Strategies to reduce cardinality:
| Strategy | Example | Impact |
|---|---|---|
| Round timestamps to the minute or hour | 08:31:47 > 08:30:00 | Reduces DateTime cardinality dramatically |
| Remove decimal places from non-financial numbers | 3.14159 > 3.14 | Reduces unique values |
| Group low-frequency text values | 500 cities with < 10 orders each > "Other" | Reduces dimension size |
| Use integer keys instead of string keys | "PROD-A-2024-001" > 1001 | Smaller column, faster joins |
| Split date and time into separate columns | "2024-01-15 08:30:00" > Date: "2024-01-15" + Time: "08:30" | Each column compresses better |
Use Appropriate Data Types
Choosing the right data type affects both model size and query performance:
| Data Type | Size per Value | When to Use |
|---|---|---|
| Whole Number (Int64) | 8 bytes (before compression) | IDs, keys, counts, quantities |
| Decimal Number (Double) | 8 bytes (before compression) | Scientific data, calculations |
| Fixed Decimal (Currency) | 8 bytes (before compression) | Financial amounts — precise to 4 decimal places |
| Text (String) | Variable (2 bytes per char) | Names, categories, descriptions |
| Date/Time | 8 bytes (before compression) | Dates and timestamps |
| TRUE/FALSE (Boolean) | 1 byte (before compression) | Flags, yes/no indicators |
Best practice: Use Whole Number for keys and IDs instead of Text. Use Fixed Decimal for financial data instead of Decimal Number (it's faster for aggregations).
Avoid Calculated Columns — Use Power Query Instead
Calculated columns are evaluated using the Formula Engine and stored in the model. They increase model size and are computed during refresh.
Instead of this calculated column:
FullName = [FirstName] & " " & [LastName]
Use this Power Query step:
= Table.AddColumn(PreviousStep, "FullName", each [FirstName] & " " & [LastName], type text)
Why Power Query is better:
- The transformation is done during data load, not during query time
- Power Query transformations can be folded back to the data source (native query)
- The result is stored as a regular column, which compresses better
- It keeps the DAX layer clean for measures only
Exception: Calculated columns that depend on relationships (using RELATED or RELATEDTABLE) cannot be done in Power Query. These are acceptable as calculated columns but should be used sparingly.
Disable Auto Date/Time
Power BI automatically creates hidden date/time tables for every date column in your model. This is useful for quick date intelligence but wasteful if you have your own date table.
To disable Auto Date/Time:
Step 1: Go to File > Options and settings > Options.
Step 2: Under Current File, click Data Load.
Step 3: Uncheck Auto date/time for new files (under Time Intelligence).
Step 4: Click OK.
Impact: If you have 10 date columns, disabling Auto Date/Time removes 10 hidden tables, each with rows for every date in the range. This can save significant memory.
Optimize Relationships
| Practice | Details |
|---|---|
| Use integer keys | Integer-to-integer relationships are fastest |
| Avoid inactive relationships | Remove relationships you do not use |
| Prefer one-to-many over many-to-many | One-to-many is simpler and faster |
| Set correct cross-filter direction | Default single direction is almost always best |
| Ensure referential integrity | In dataset settings, mark "Assume referential integrity" for DirectQuery |
Reducing Model Size
Why Model Size Matters
| Impact Area | How Size Affects It |
|---|---|
| Memory consumption | Larger models consume more RAM on the capacity |
| Refresh time | Larger models take longer to compress and load |
| Dataset limits | Pro: 1 GB, PPU: 100 GB, Premium: 10–400 GB depending on SKU |
| Sharing performance | Larger models take longer to load when first opened |
| Gateway throughput | Larger datasets mean more data transferred through the gateway |
Identifying Large Tables and Columns
Use DAX Studio with VertiPaq Analyzer to identify the largest components:
Step 1: Open DAX Studio and connect to your Power BI Desktop model.
Step 2: Navigate to Advanced > View Metrics (VertiPaq Analyzer).
Step 3: Review the Tables tab — sorted by size.
Step 4: Drill into columns within large tables — sorted by size.
Step 5: Look for columns with high cardinality (many unique values) and large dictionary size.
Common Size Offenders
| Column Type | Why It's Large | What to Do |
|---|---|---|
| High-cardinality text (e.g., descriptions, comments) | Many unique values, poor compression | Remove if not needed; summarize if needed |
| GUIDs and unique IDs | Every value is unique (worst-case cardinality) | Replace with integer surrogate keys |
| Precise timestamps | Seconds/milliseconds create many unique values | Round to minute or hour |
| Large decimal numbers | Random decimals don't compress well | Round to needed precision |
| Denormalized columns | Repeated data in fact tables | Normalize into dimension tables |
Aggregations (User-Defined)
For very large datasets (hundreds of millions or billions of rows), Power BI supports user-defined aggregations:
- Create a summary (aggregated) table alongside your detail table
- Define aggregation mappings (SUM, COUNT, MIN, MAX, GROUP BY)
- Power BI automatically routes queries to the aggregation table when possible
- When a query needs detail-level data, it falls through to the detail table
Example:
Detail table (FactSales): 500M rows, stored in DirectQuery mode
Aggregation table (FactSales_Agg): 100K rows, stored in Import mode
| Aggregation Column | Detail Column | Function |
|---|---|---|
| Agg_Amount | Amount | SUM |
| Agg_Quantity | Quantity | SUM |
| Agg_RowCount | (table) | COUNT |
| ProductKey | ProductKey | GROUP BY |
| DateKey | DateKey | GROUP BY |
| RegionKey | RegionKey | GROUP BY |
Queries that aggregate by Product, Date, and Region hit the small Import table (fast). Queries that need individual transactions fall through to DirectQuery (slower but available).
Star Schema for Performance
Why Star Schema is Faster
The star schema is the recommended data model design for Power BI. It consists of:
- Fact tables — Narrow tables with numeric measures and foreign keys (transaction data)
- Dimension tables — Wide tables with descriptive attributes (lookup data)
DimDate
│
DimProduct ── FactSales ── DimRegion
│
DimCustomer
Performance Benefits
| Benefit | Explanation |
|---|---|
| Fewer joins | Each dimension connects directly to the fact table — no chain of joins |
| Better compression | Fact tables are narrow (few columns) and contain integers/numbers that compress well |
| Efficient filtering | Filters on dimensions propagate cleanly to the fact table |
| Predictable query plans | The Storage Engine optimizes well for star schemas |
| RLS compatibility | Security filters on dimensions propagate naturally to facts |
| Simpler DAX | Measures are cleaner when written against a well-designed star schema |
Denormalization for Performance
In a star schema, dimensions are denormalized — attributes from related lookup tables are merged into a single dimension table:
Instead of this snowflake structure:
Product → SubCategory → Category → Department
Use this denormalized star structure:
DimProduct (ProductName, SubCategory, Category, Department)
└── FactSales
Why: Each join adds query complexity. A denormalized dimension eliminates joins and keeps the model simple.
Narrow Fact Tables
Keep fact tables as narrow as possible:
| Include | Exclude |
|---|---|
| Foreign keys to dimensions (integer) | Descriptive text (put in dimensions) |
| Numeric measures (amount, quantity, cost) | Calculated fields (use DAX measures) |
| Date keys | Redundant columns |
| Degenerate dimensions (order number) | Columns duplicated from dimensions |
Example — Optimized fact table:
| OrderKey | DateKey | ProductKey | CustomerKey | RegionKey | Amount | Quantity | Cost |
|---|---|---|---|---|---|---|---|
| 1001 | 20240115 | 42 | 1587 | 3 | 500.00 | 2 | 320.00 |
Eight columns, all integers or decimals. This compresses extremely well.
DAX Optimization
DAX performance has a direct impact on report interactivity. The difference between a well-written and poorly-written measure can be orders of magnitude.
Use Variables to Avoid Repeated Calculations
Variables (VAR) compute a value once and reuse it. Without variables, the same subexpression may be evaluated multiple times.
Before (slower):
Profit Margin =
DIVIDE(
SUM(Sales[Amount]) - SUM(Sales[Cost]),
SUM(Sales[Amount])
)
In this example, SUM(Sales[Amount]) is evaluated twice.
After (faster):
Profit Margin =
VAR TotalAmount = SUM(Sales[Amount])
VAR TotalCost = SUM(Sales[Cost])
RETURN
DIVIDE(TotalAmount - TotalCost, TotalAmount)
Now SUM(Sales[Amount]) is evaluated once and reused.
Avoid FILTER(table) — Use Column Filters
Using FILTER on an entire table is expensive because it iterates row by row through the Formula Engine. Using column filters instead allows the Storage Engine to handle the work.
Before (slower):
East Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(Sales, Sales[Region] = "East")
)
This forces the Formula Engine to iterate all rows in the Sales table.
After (faster):
East Sales =
CALCULATE(
SUM(Sales[Amount]),
Sales[Region] = "East"
)
This passes the filter directly to the Storage Engine, which handles it natively with compressed column scans.
When FILTER Is Necessary
Use FILTER only when you need a condition that involves:
- Multiple columns in the same row (e.g.,
FILTER(Sales, Sales[Amount] > Sales[Cost])) - A measure result (e.g.,
FILTER(Products, [Total Sales] > 1000)) - Complex logic that cannot be expressed as a simple column filter
Even then, filter on the smallest possible table:
Before (slower):
High Value Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(Sales, Sales[Amount] > 1000)
)
After (faster — if a dimension can be used):
High Value Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(ALL(Sales[Amount]), Sales[Amount] > 1000)
)
Using ALL(Sales[Amount]) iterates only the unique values of the Amount column, not every row in the Sales table.
CALCULATE with Column Filters vs Table Filters
| Pattern | Engine Used | Performance |
|---|---|---|
CALCULATE(expr, Table[Column] = "value") | Storage Engine | Fast |
CALCULATE(expr, FILTER(ALL(Table[Column]), condition)) | Storage Engine | Good |
CALCULATE(expr, FILTER(Table, condition)) | Formula Engine | Slow |
KEEPFILTERS vs REMOVEFILTERS
KEEPFILTERS modifies how CALCULATE interacts with existing filters:
-- Without KEEPFILTERS: replaces the existing Region filter
East Sales =
CALCULATE(SUM(Sales[Amount]), Sales[Region] = "East")
-- With KEEPFILTERS: intersects with the existing Region filter
East Sales =
CALCULATE(SUM(Sales[Amount]), KEEPFILTERS(Sales[Region] = "East"))
Performance tip: KEEPFILTERS is slightly faster in some scenarios because it narrows the data set rather than replacing the filter context.
Avoid Complex Iterators on Large Tables
Iterator functions like SUMX, AVERAGEX, COUNTX, MAXX, and MINX evaluate an expression for each row in the table. On large tables (millions of rows), this can be extremely slow.
Before (slower — iterating millions of rows):
Weighted Average Price =
SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) / SUM(Sales[Quantity])
After (faster — if a pre-calculated column exists):
Weighted Average Price =
DIVIDE(SUM(Sales[LineTotal]), SUM(Sales[Quantity]))
Where LineTotal is a column calculated in Power Query as Quantity * UnitPrice. This avoids the row-by-row iteration entirely.
SUMMARIZECOLUMNS vs SUMMARIZE
SUMMARIZECOLUMNS is generally faster than SUMMARIZE for generating summary tables:
| Function | Performance | Notes |
|---|---|---|
SUMMARIZECOLUMNS | Faster | Optimized for query generation; cannot be used inside other iterators |
SUMMARIZE | Slower | Can be nested inside iterators; older function |
ADDCOLUMNS + VALUES | Moderate | Alternative pattern; explicit and controllable |
Recommendation: For calculated tables or DAX queries, prefer SUMMARIZECOLUMNS. For use inside measures (where SUMMARIZECOLUMNS is restricted), use ADDCOLUMNS(VALUES(Dim[Column]), ...).
DAX Optimization Summary Table
| Practice | Before (Slow) | After (Fast) |
|---|---|---|
| Use variables | DIVIDE(SUM(X) - SUM(Y), SUM(X)) | VAR a = SUM(X) VAR b = SUM(Y) RETURN DIVIDE(a - b, a) |
| Column filter | FILTER(Sales, Sales[Region]="East") | Sales[Region] = "East" |
| Reduce iterator scope | SUMX(Sales, ...) on 10M rows | Pre-compute column in Power Query |
| Avoid nested CALCULATE | CALCULATE(CALCULATE(...)) | Flatten into single CALCULATE with multiple filters |
| Use DIVIDE | IF(denominator = 0, 0, num/denom) | DIVIDE(num, denom, 0) |
| Use SELECTEDVALUE | IF(HASONEVALUE(T[C]), VALUES(T[C])) | SELECTEDVALUE(T[C]) |
Query Folding
Query folding is one of the most important performance features in Power Query. When it works, transformations are translated into native database queries and executed at the source — the data is filtered and transformed before it arrives in Power BI.
What It Is
When you apply transformations in Power Query (filter rows, select columns, group by), Power Query attempts to translate those steps into the native query language of the data source (e.g., SQL for databases, OData for web services).
Without folding: Power BI downloads all data and processes it locally.
With folding: The database processes the filter/transform and sends only the results.
Why It Matters
| Aspect | With Folding | Without Folding |
|---|---|---|
| Data transferred | Only result rows | All rows, then filtered locally |
| Processing location | Source database (optimized) | Power BI (Formula Engine) |
| Refresh time | Faster | Slower |
| Memory usage | Lower | Higher |
| Scalability | Handles large sources | Limited by local resources |
Checking If Folding Occurs
Step 1: In Power Query Editor, right-click on a step.
Step 2: Look for View Native Query in the context menu.
- If View Native Query is available and not grayed out — the step is folded
- If View Native Query is grayed out — the step is not folded (or the source does not support viewing native queries)
Step 3: Click View Native Query to see the generated SQL or native query.
Steps That Break Folding
Once a step breaks folding, all subsequent steps also run locally. Common folding breakers:
| Transformation | Foldable? | Notes |
|---|---|---|
| Filter rows (simple comparison) | Yes | Basic comparisons fold |
| Select/Remove columns | Yes | Column projection folds |
| Sort rows | Yes (usually) | Folds for most databases |
| Group by | Yes (usually) | Folds for most databases |
| Merge queries (join) | Yes (sometimes) | Folds if both tables are from the same source |
| Add custom column (simple) | Sometimes | Simple arithmetic may fold |
| Add custom column (complex M) | No | Custom M functions break folding |
| Pivot/Unpivot | No (usually) | Most sources do not support this natively |
| Change type (some types) | Sometimes | Depends on source capabilities |
| Add index column | No | No native equivalent in most sources |
| Buffer table | No | Explicitly breaks folding |
| Replace values (complex) | Sometimes | Simple replacements may fold |
Maximizing Folding
- Apply foldable steps first — Filters and column selections should come before non-foldable transformations
- Use the source's native capabilities — If your source supports it, let Power Query fold the work
- Avoid unnecessary steps — Each step that breaks folding prevents all subsequent steps from folding
- Use database views — If complex transformation logic breaks folding, encapsulate it in a database view
- Check folding after each step — Right-click and verify "View Native Query" is available
DirectQuery Performance
DirectQuery mode queries the source database in real-time instead of importing data. This is necessary for very large datasets or real-time data requirements, but it introduces performance challenges.
Challenges with DirectQuery
| Challenge | Why It Occurs |
|---|---|
| Every interaction generates a query | Unlike Import mode, there is no local cache — each slicer change hits the source |
| Source database performance | Power BI is only as fast as the source database |
| Network latency | Round-trip time to the database adds up |
| Concurrent users | Multiple users generate multiple queries, stressing the source |
| Limited DAX support | Some DAX patterns generate inefficient SQL |
| No in-memory caching | Each visual refresh fetches data from scratch |
Dual Storage Mode
Dual storage mode allows a table to be stored in both Import and DirectQuery modes simultaneously:
- When the table is used in a query with other Import tables, the Import copy is used (fast)
- When the table is used in a query with DirectQuery tables, the DirectQuery mode is used
- Best applied to dimension tables (small, frequently filtered) while keeping fact tables in DirectQuery
Composite Models
Composite models combine Import and DirectQuery tables in the same dataset:
| Table | Storage Mode | Reason |
|---|---|---|
| FactSales (500M rows) | DirectQuery | Too large to import |
| DimDate | Dual | Small, frequently filtered |
| DimProduct | Dual | Small, frequently filtered |
| DimRegion | Import | Very small, rarely changes |
| FactBudget | Import | Small, separately managed |
Source Database Optimization
Since DirectQuery performance depends on the source database, optimize it:
| Optimization | Description |
|---|---|
| Create indexes | Index columns used in filters, joins, and GROUP BY clauses |
| Materialized views | Pre-compute common aggregations at the database level |
| Partitioning | Partition large tables by date for faster scans |
| Statistics updates | Keep database statistics current for optimal query plans |
| Connection pooling | Configure the source to handle concurrent connections efficiently |
| Reduce query complexity | Simplify the generated SQL by simplifying the Power BI model |
Aggregation Tables for DirectQuery
Combine Import-mode aggregation tables with DirectQuery detail tables:
- Create a summarized version of the fact table with key dimensions and aggregated measures
- Import this summary table (fast, in-memory)
- Keep the detail table in DirectQuery (for drill-through or detail-level queries)
- Power BI automatically routes queries to the aggregation when possible
Visual Optimization
Even with a perfectly optimized data model and DAX, too many visuals or poorly configured visuals can degrade performance.
Reduce the Number of Visuals Per Page
Each visual on a page generates its own DAX query. More visuals mean more queries, more rendering, and longer page load times.
| Visuals Per Page | Expected Performance | Recommendation |
|---|---|---|
| 1–5 | Excellent | Ideal for focused dashboards |
| 6–8 | Good | Acceptable for detailed analysis pages |
| 9–12 | Moderate | Consider splitting into multiple pages |
| 13–20 | Poor | Users experience noticeable lag |
| 20+ | Critical | Refactor immediately — split into multiple pages |
Avoid High-Cardinality Visuals
Visuals that display many data points are slow to render:
| Visual Configuration | Data Points | Impact |
|---|---|---|
| Card with a single value | 1 | Minimal |
| Bar chart with 10 categories | 10 | Minimal |
| Table with 100 rows, 5 columns | 500 | Low |
| Table with 1,000 rows, 10 columns | 10,000 | Moderate — consider pagination |
| Scatter chart with 10,000 points | 10,000 | High — consider sampling |
| Matrix with 500 rows x 50 columns | 25,000 | Very high — use drillthrough instead |
| Table with 100,000 rows | 100,000+ | Unacceptable — aggregate the data |
Use Bookmarks Instead of Too Many Visuals
Instead of displaying 20 visuals on one page:
- Create multiple views using different sets of visuals
- Use bookmarks to capture each view state
- Add bookmark navigator buttons for users to switch between views
- Only the visuals visible in the current bookmark generate queries
This dramatically reduces the number of concurrent queries per page load.
Limit Visual Interactions
By default, every visual on a page cross-filters and cross-highlights every other visual. When a user clicks on a bar in a chart, every other visual re-queries and re-renders.
To disable unnecessary interactions:
Step 1: Select the visual you want to control.
Step 2: Go to Format tab > Edit interactions.
Step 3: For each other visual on the page, choose:
- Filter — The visual filters the other visual
- Highlight — The visual highlights matching data in the other visual
- None — No interaction (best for performance)
Disable interactions between visuals that do not need to interact. This reduces the cascade of re-queries when users click.
The Optimize Ribbon Feature
Power BI Desktop includes an Optimize feature for visuals:
- Reduce visuals — Identifies visuals that could be consolidated
- Reduce data — Identifies visuals loading excessive data
- Performance tips — Suggests improvements based on visual configuration
Access it from the Optimize tab in the ribbon (available in recent versions).
Incremental Refresh
Incremental refresh allows you to refresh only the most recent data instead of the entire dataset, dramatically reducing refresh time for large datasets.
Benefits
| Benefit | Description |
|---|---|
| Faster refresh | Only new/changed data is loaded — unchanged data is preserved |
| Reduced source load | Fewer queries and less data transferred from the source |
| Larger datasets | You can maintain years of historical data without refreshing it all |
| Reliability | Shorter refresh windows reduce the chance of timeout failures |
| Cost efficiency | Less capacity usage during refresh |
How It Works
Incremental refresh divides the dataset into partitions based on a date column:
- Historical partitions — Older data that is never refreshed after initial load
- Incremental partitions — Recent data that is refreshed on schedule
- Real-time partition — (Optional) Current data that can use DirectQuery for near real-time
Configuring Incremental Refresh
Step 1: In Power Query, create two parameters:
- RangeStart — DateTime type, initial value: a past date
- RangeEnd — DateTime type, initial value: today's date
Step 2: Filter the date column in your table to be between RangeStart and RangeEnd.
Step 3: Go to the table's context menu and select Incremental refresh.
Step 4: Configure the policy:
| Setting | Description | Example |
|---|---|---|
| Archive data starting | How far back to keep data | 5 years |
| Incrementally refresh data starting | How recent data to refresh each time | 10 days |
| Detect data changes | Refresh only if data has changed (uses a max-date column) | LastModifiedDate |
| Only refresh complete days | Skip the current day to avoid partial data | Yes |
| Get the latest data in real time | Add a DirectQuery partition for the current period | Optional |
Step 5: Publish to Power BI Service. The incremental refresh policy is applied during Service-side refresh.
Important Notes
- Incremental refresh requires the date filter to use query folding — the source must support the RangeStart/RangeEnd filter natively
- The RangeStart and RangeEnd parameters must not be renamed — Power BI looks for these exact names
- Incremental refresh is configured in Desktop but only executes in the Service
- Works with Import mode tables only (or Import mode partitions in composite models)
Report-Level Optimization
Beyond the data model and DAX, the overall report design affects performance.
Reduce Pages
- Each page with visible content generates queries for all its visuals
- Users often navigate through pages sequentially, so fewer pages means a faster experience
- Consolidate related information onto fewer pages using bookmarks and drillthrough
Use Hidden Pages for Drillthrough
Instead of showing detail data on the main page:
- Create a dedicated drillthrough page with the detail visual
- Mark the page as a drillthrough target
- The page is only loaded when a user actively drills through — not during initial page load
Disable Visual Interactions Where Not Needed
As described in the Visual Optimization section, disable cross-filtering between visuals that do not need to interact. This reduces the number of queries generated when users click on visuals.
Limit Cross-Filtering
If a page has 10 visuals and all cross-filter each other, a single click generates up to 9 re-queries (one for each other visual). Reducing interactions to only the necessary ones can cut this dramatically.
Optimize Background Images
If you use background images on report pages:
| Image Format | Size (1920x1080) | Performance Impact |
|---|---|---|
| Uncompressed PNG | 5–10 MB | Slow page load, especially on mobile |
| Optimized PNG | 500 KB – 1 MB | Acceptable |
| JPEG (80% quality) | 200–500 KB | Good — recommended for photos |
| SVG | Variable | Best for logos and simple graphics |
| WebP | 100–300 KB | Best compression but limited support |
Recommendation: Keep background images under 500 KB. Use JPEG for photographic backgrounds and SVG or optimized PNG for logos and geometric designs.
Monitoring and Troubleshooting
For advanced performance analysis, external tools provide deeper insights than Performance Analyzer.
DAX Studio
DAX Studio is a free, open-source tool for analyzing and optimizing DAX queries.
Connecting to a Dataset:
Step 1: Download and install DAX Studio from daxstudio.org.
Step 2: If connecting to a local model, open Power BI Desktop first.
Step 3: Launch DAX Studio and select the Power BI Desktop model (or a published dataset via XMLA endpoint).
Step 4: Click Connect.
Running DAX Queries
In DAX Studio, you can run any DAX query against the model:
EVALUATE
SUMMARIZECOLUMNS(
DimProduct[Category],
"Total Sales", SUM(FactSales[Amount]),
"Order Count", COUNTROWS(FactSales)
)
This helps you test and optimize measure logic in isolation, without the overhead of visual rendering.
Server Timings
Step 1: In DAX Studio, enable Server Timings from the toolbar.
Step 2: Run your DAX query.
Step 3: The Server Timings pane shows:
| Metric | What It Means |
|---|---|
| Total | Total query execution time |
| SE (Storage Engine) | Time spent scanning VertiPaq data |
| FE (Formula Engine) | Time spent evaluating DAX expressions |
| SE Queries | Number of Storage Engine queries generated |
| SE Cache | Whether results came from cache (hot) or cold storage |
Interpreting Server Timings
| Observation | Diagnosis | Action |
|---|---|---|
| High FE time, low SE time | Complex DAX causing Formula Engine work | Simplify DAX, use variables, avoid iterators |
| High SE time, low FE time | Large data scans | Reduce cardinality, add aggregations |
| Many SE queries | Complex measure generating many sub-queries | Simplify measure logic, reduce visual complexity |
| SE Cache = Yes | Results served from cache | Good — this is optimal for repeated queries |
| SE Cache = No | Cold query — data scanned from disk/memory | First query after refresh; expected behavior |
VertiPaq Analyzer
VertiPaq Analyzer (accessible via DAX Studio > Advanced > View Metrics) provides detailed information about the data model:
| Information | Why It's Useful |
|---|---|
| Table sizes (rows, columns, bytes) | Identify the largest tables |
| Column sizes and cardinality | Find columns that consume the most memory |
| Relationship sizes | Identify costly relationships |
| Partition information | Understand data distribution |
| Encoding type | Check if columns use optimal encoding |
| Dictionary size | Large dictionaries indicate high cardinality |
Using VertiPaq Analyzer Output
Step 1: Export VertiPaq Analyzer data to a .vpax file.
Step 2: Open the file in DAX Optimizer (online tool) or analyze it in Power BI itself.
Step 3: Focus on:
- Columns consuming more than 10% of the total model size
- Columns with cardinality greater than 1 million (consider reducing)
- Tables with more than 20 columns (consider removing unused columns)
Optimization Checklist
Use this comprehensive checklist when optimizing any Power BI report:
Data Model Checklist
| Item | Status | Action |
|---|---|---|
| Remove unused columns | [ ] | Delete columns not used in visuals, measures, or relationships |
| Remove unused tables | [ ] | Delete tables not referenced in the model |
| Use integer keys | [ ] | Replace text-based keys with integer surrogates |
| Optimize data types | [ ] | Use Whole Number for IDs; Fixed Decimal for currency |
| Disable Auto Date/Time | [ ] | Turn off if you have a dedicated date table |
| Star schema design | [ ] | Ensure fact tables connect to dimension tables |
| Narrow fact tables | [ ] | Move text attributes to dimension tables |
| Reduce cardinality | [ ] | Round timestamps, group rare values, use integer keys |
| Remove calculated columns | [ ] | Replace with Power Query steps where possible |
DAX Checklist
| Item | Status | Action |
|---|---|---|
| Use variables | [ ] | Replace repeated subexpressions with VARs |
| Column filters in CALCULATE | [ ] | Use Table[Column] = "value" instead of FILTER(Table, ...) |
| Avoid FILTER on entire tables | [ ] | Use FILTER(ALL(Table[Column]), ...) instead |
| Minimize iterators on large tables | [ ] | Pre-compute columns in Power Query to avoid SUMX on millions of rows |
| Use DIVIDE function | [ ] | Replace manual division with DIVIDE(num, denom, alt) |
| Use SELECTEDVALUE | [ ] | Replace IF(HASONEVALUE(...), VALUES(...)) pattern |
| Avoid nested CALCULATE | [ ] | Flatten to a single CALCULATE with multiple filters |
| Keep measures simple | [ ] | Break complex measures into smaller sub-measures using variables |
Visual Checklist
| Item | Status | Action |
|---|---|---|
| Visuals per page < 10 | [ ] | Split busy pages, use bookmarks for alternative views |
| No high-cardinality tables | [ ] | Limit table/matrix visuals to paginated data or drillthrough |
| Interactions disabled where not needed | [ ] | Set interaction to "None" between unrelated visuals |
| Drillthrough for detail pages | [ ] | Move detail tables to hidden drillthrough pages |
| Background images optimized | [ ] | Keep under 500 KB; use JPEG or SVG |
| Minimal cross-filtering | [ ] | Only enable cross-filter between visuals that meaningfully interact |
Refresh and Data Source Checklist
| Item | Status | Action |
|---|---|---|
| Query folding verified | [ ] | Right-click steps in Power Query to check "View Native Query" |
| Incremental refresh configured | [ ] | Set up for large tables with date columns |
| Gateway performance adequate | [ ] | Monitor gateway machine CPU and memory |
| Data source indexed | [ ] | Ensure database indexes exist for filtered and joined columns |
| Scheduled refresh within limits | [ ] | Pro: 8/day; Premium: 48/day; keep refresh windows reasonable |
| Credentials and connectivity tested | [ ] | Verify data source credentials are valid and gateway is online |
Advanced Checklist
| Item | Status | Action |
|---|---|---|
| Performance Analyzer run | [ ] | Record and analyze all visuals; address items over 1 second |
| DAX Studio Server Timings reviewed | [ ] | Check FE vs SE time; optimize high-FE measures |
| VertiPaq Analyzer reviewed | [ ] | Identify largest columns and tables; reduce where possible |
| Aggregation tables considered | [ ] | For datasets over 100M rows, implement user-defined aggregations |
| Composite model evaluated | [ ] | Use Import for small tables, DirectQuery for very large tables |
Practice Exercises
Exercise 1 — Performance Analyzer Investigation
- Open a Power BI report with at least five visuals on one page
- Open Performance Analyzer (View tab > Performance Analyzer)
- Click Start recording and then Refresh visuals
- Identify the three slowest visuals by total duration
- For each slow visual, determine whether the bottleneck is DAX query time, visual display time, or other
- Document your findings and propose optimizations for each
Exercise 2 — Data Model Optimization
- Open a Power BI Desktop file (or create one with sample data)
- List all columns in the model and identify at least five unused columns
- Remove the unused columns
- Check for any columns that could use a more efficient data type (e.g., text IDs that could be integers)
- Disable Auto Date/Time if a dedicated date table exists
- Compare the model size before and after (File > Info shows approximate size)
Exercise 3 — DAX Optimization
Take the following measures and optimize them. Test before and after in Performance Analyzer.
Measure 1 (uses FILTER on entire table):
High Value Orders =
CALCULATE(
COUNTROWS(Sales),
FILTER(Sales, Sales[Amount] > 1000)
)
Measure 2 (repeated calculations without variables):
YoY Growth =
DIVIDE(
SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(DimDate[Date])),
CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(DimDate[Date]))
)
Measure 3 (uses IF instead of DIVIDE):
Avg Price =
IF(
SUM(Sales[Quantity]) = 0,
BLANK(),
SUM(Sales[Amount]) / SUM(Sales[Quantity])
)
Rewrite each measure using the optimization techniques from this chapter and compare performance.
Exercise 4 — Query Folding Verification
- In Power Query, connect to a database source (or use an OData feed)
- Apply the following steps in order: Filter rows, Select columns, Group by, Add a custom column
- After each step, right-click and check if "View Native Query" is available
- Identify which step breaks query folding
- Rearrange the steps to maximize folding (move foldable steps before non-foldable ones)
Exercise 5 — Visual Optimization
- Create a report page with 15 visuals (charts, tables, cards)
- Record performance with Performance Analyzer
- Note the total page load time
- Apply the following optimizations: a. Remove 5 visuals and move their data to a drillthrough page b. Disable interactions between at least 3 pairs of visuals c. Replace a high-cardinality table (showing 1000+ rows) with a chart or top-N table
- Record performance again and compare the improvement
Exercise 6 — Incremental Refresh Configuration
- Open a Power BI Desktop file with a large table that has a date column
- Create the
RangeStartandRangeEndparameters in Power Query - Filter the date column using these parameters
- Configure incremental refresh to archive 2 years of data and refresh the last 7 days
- Publish to Power BI Service (Premium or PPU workspace required)
- Verify the refresh policy is applied by checking the dataset settings
Exercise 7 — DAX Studio Deep Dive
- Install DAX Studio (daxstudio.org)
- Connect to a Power BI Desktop model
- Enable Server Timings
- Run a simple query:
EVALUATE SUMMARIZECOLUMNS(DimProduct[Category], "Sales", SUM(FactSales[Amount])) - Note the SE and FE times
- Run a complex query with FILTER on a large table and compare SE/FE times
- Open VertiPaq Analyzer and identify the three largest columns in the model
- Document which columns you would target for optimization
Summary
Performance optimization is not a one-time task but an ongoing discipline that should be part of every Power BI project. In this chapter, you learned:
- Performance directly impacts adoption — Users abandon slow reports, so optimization is a business necessity, not just a technical nicety
- The Power BI engine consists of the Storage Engine (fast, multithreaded, columnar scans) and the Formula Engine (slower, single-threaded, row-by-row processing) — optimize to maximize Storage Engine usage
- Performance Analyzer is the built-in tool for identifying bottlenecks, showing DAX query time, visual display time, and other processing time for each visual
- Data model optimization has the highest impact — remove unused columns, reduce cardinality, use integer keys, disable Auto Date/Time, and design star schemas with narrow fact tables
- Model size reduction through removing high-cardinality text columns, using aggregation tables, and normalizing repeated data into dimension tables
- Star schema design is faster because it reduces joins, improves compression, and enables predictable query plans
- DAX optimization focuses on using variables, column filters instead of FILTER(table), avoiding complex iterators on large tables, and using efficient function patterns
- Query folding translates Power Query transformations into native database queries, dramatically reducing data transfer and processing time
- DirectQuery performance depends on the source database — use dual storage mode, composite models, aggregation tables, and source-level optimization
- Visual optimization targets the number of visuals per page (under 10 is ideal), high-cardinality visuals, unnecessary cross-filtering, and background image sizes
- Incremental refresh refreshes only recent data, making large dataset refreshes faster and more reliable
- DAX Studio and VertiPaq Analyzer are essential external tools for deep performance analysis, providing Server Timings and detailed model metrics
Congratulations!
You have completed the Power BI tutorial series. Over these chapters, you have journeyed from the fundamentals of data import and modeling through advanced DAX, visualizations, Power BI Service, Row-Level Security, and now Performance Optimization. You now have a comprehensive foundation for building professional-grade Power BI solutions.
Next Steps
To continue your Power BI journey, consider these paths:
- Microsoft Certification PL-300 — The Microsoft Power BI Data Analyst certification validates your skills and is widely recognized by employers. Study topics align closely with what you have learned in this series.
- Advanced DAX — Explore topics like calculation groups, field parameters, dynamic format strings, and complex time intelligence patterns.
- Power BI Embedded — Learn to embed Power BI reports in custom web applications using the JavaScript API and REST APIs.
- Microsoft Fabric — The next evolution of Microsoft's analytics platform, integrating Power BI with data engineering, data science, and real-time analytics in a unified lakehouse architecture.
- Community Resources — Join the Power BI Community (community.powerbi.com), follow the Power BI Blog, subscribe to Guy in a Cube and SQLBI YouTube channels, and attend local Power BI user group meetings.
- Practice — The most effective way to solidify your skills is to build real projects. Find a dataset that interests you, build a complete solution from data import through published report, and iterate based on feedback.
Your Power BI journey is just beginning. The skills you have built here will serve as a strong foundation for whatever you build next.