Performance Optimization

Performance Optimization

Overview

Power BI performance depends on data model design, DAX efficiency, visual configuration, and infrastructure. This skill covers diagnostic tools, optimization techniques, and best practices for achieving fast, responsive reports.

Diagnostic Tools

Performance Analyzer (Built-in)

Enable in Power BI Desktop: View > Performance Analyzer > Start recording

Workflow: 1. Start recording 2. Clear visual cache (click "Refresh visuals") 3. Interact with the report (change slicers, navigate pages) 4. Copy DAX query from slow visuals 5. Paste into DAX Studio for deeper analysis

DAX Studio

Free external tool for deep DAX performance analysis:

Key features:

• Execute DAX queries with timing
• Server Timings: shows Storage Engine (SE) vs Formula Engine (FE) time
• Query Plan: view logical and physical query plans
• VertiPaq Analyzer: model size and compression analysis
• All Queries trace: capture all queries sent by a report

Server Timings breakdown:

Ideal ratio: SE should be 80-90% of total time. High FE % means DAX is doing too much computation.

Common DAX Studio workflow: 1. Connect to Power BI Desktop (or XMLA endpoint) 2. Enable Server Timings (Query > Server Timings) 3. Paste the DAX query from Performance Analyzer 4. Execute and analyze timing breakdown 5. Look for:

• Many SE queries (indicates materialization issues)
• CallbackDataID in SE queries (data sent to FE for processing -- avoid)
• High FE time (DAX too complex)
• Large SE row counts (too much data scanned)

VertiPaq Analyzer

Analyze model size and compression in DAX Studio: Advanced > View Metrics

Data Model Optimization

Column Optimization

Relationship Optimization

• Use single-direction cross-filtering (avoid bidirectional)
• Enable "Assume Referential Integrity" for DirectQuery relationships
• Remove unused or redundant relationships
• Use integer key columns for relationships

Partition Strategy

For large tables, partition by date range:

• Historical partitions (yearly/quarterly) -- refresh rarely
• Recent partition (current month/week) -- refresh frequently
• Use incremental refresh to automate partition management

DAX Optimization

High-Impact Patterns

Use variables to avoid repeated calculations:

// BAD: Calculates [Total Sales] three times
Margin % = DIVIDE([Total Sales] - [Total Cost], [Total Sales])

// GOOD: Single calculation, reuse via variable
Margin % =
VAR Sales = [Total Sales]
VAR Cost = [Total Cost]
RETURN DIVIDE(Sales - Cost, Sales)

Avoid FILTER with large tables in CALCULATE:

// BAD: Scans entire table
CALCULATE([Sales], FILTER(ALL(Products), Products[Category] = "Electronics"))

// GOOD: Column filter (optimized)
CALCULATE([Sales], Products[Category] = "Electronics")

Avoid nested iterators:

// BAD: O(n^2) complexity
SUMX(Products,
    SUMX(FILTER(Sales, Sales[ProductID] = Products[ProductID]),
        Sales[Amount]))

// GOOD: Use relationship + simple aggregation
SUMX(Products, [Total Sales])

Use DISTINCTCOUNT instead of COUNTROWS(DISTINCT(...)):

// BAD
COUNTROWS(DISTINCT(Sales[CustomerID]))

// GOOD
DISTINCTCOUNT(Sales[CustomerID])

Avoid FORMAT() in measures (returns text, kills sort):

// BAD: Returns text, cannot sort
MonthLabel = FORMAT([Date], "MMMM yyyy")

// GOOD: Use a pre-computed column in the Date table for display
// And a numeric sort column for ordering

Measure Complexity Guidelines

Visual Optimization

Reduce Visual Count

Query Reduction

Enable query reduction features: 1. Report settings > Query reduction > Add Apply button to slicers -- users click "Apply" after all slicer changes 2. Reduce number of queries sent by > Disable cross-highlighting by default -- reduces inter-visual queries

Conditional Formatting

Avoid complex DAX-based conditional formatting on large tables. Use simple column references or measures with limited computation.

Advanced Capacity and Model Patterns

Detailed guidance for aggregations, composite models, Direct Lake performance, large dataset optimization (10GB+ semantic models), Power BI Desktop performance settings, Power BI Report Server tuning, and bookmark/filter optimization lives in references/advanced-capacity-patterns.md. Load that reference when tuning enterprise-scale models beyond basic DAX/model/visual improvements.

Performance Checklist

Data Model

• [ ] Star schema design (fact + dimension tables)
• [ ] Auto date/time disabled
• [ ] No unused columns
• [ ] Integer keys for relationships
• [ ] Single-direction cross-filtering
• [ ] Text columns only in dimension tables
• [ ] Calculated columns converted to measures where possible
• [ ] High-cardinality columns addressed

DAX

• [ ] Variables used for repeated expressions
• [ ] No FILTER on large tables in CALCULATE
• [ ] No nested iterators
• [ ] DISTINCTCOUNT preferred over COUNTROWS(DISTINCT(...))
• [ ] No FORMAT in measures used for sorting
• [ ] Measures return numeric types (not text)

Visuals

• [ ] 8-12 visuals per page maximum
• [ ] Apply button on slicers
• [ ] Top N applied on large tables
• [ ] Cross-highlighting minimized for heavy pages
• [ ] Conditional formatting uses simple expressions

Infrastructure

• [ ] Correct capacity size for workload
• [ ] Premium/Fabric for large models (>1GB)
• [ ] Gateway optimized (sufficient RAM, SSD, close to data source)
• [ ] Incremental refresh for large tables
• [ ] Aggregations for DirectQuery heavy queries

Direct Lake (if applicable)

• [ ] V-Order enabled on delta table writes
• [ ] Framing scheduled at appropriate frequency
• [ ] File/row-group counts within capacity guardrails
• [ ] Fallback behavior configured and monitored
• [ ] Calculated columns tested for fallback impact

Report Server (if applicable)

• [ ] Report server DB isolated from PBIRS process
• [ ] Sufficient CPU cores for peak concurrent users
• [ ] SSD storage with high IOPS for DB
• [ ] Report caching configured for popular reports
• [ ] Scale-out with NLB if >50 concurrent users

Additional Resources

Reference Files

• `references/dax-studio-walkthrough.md` -- Step-by-step DAX Studio analysis guide with query plan interpretation and latest DAX Studio features

Power BI Advanced Performance Patterns

Detailed guidance for aggregations, composite models, Direct Lake performance, large dataset optimization (10GB+ semantic models), Power BI Desktop performance settings, Power BI Report Server tuning, and bookmark/filter optimization. SKILL.md keeps diagnostic tools, model optimization, DAX optimization, visual optimization, and the checklist.

Aggregations

Pre-aggregated tables that Power BI queries instead of the detail table:

Setup

1. Create an aggregation table (Import) with pre-computed aggregates:

SELECT
    ProductCategory,
    CAST(OrderDate AS DATE) AS OrderDate,
    SUM(Amount) AS TotalAmount,
    COUNT(*) AS OrderCount
FROM Sales
GROUP BY ProductCategory, CAST(OrderDate AS DATE)

2. In Power BI, set up aggregation mappings:

• Table > Manage aggregations
• Map: TotalAmount summarization Sum to detail column Sales[Amount]
• Map: OrderCount summarization Count to detail table Sales
• Map: ProductCategory group-by to Sales[ProductCategory]
• Map: OrderDate group-by to Sales[OrderDate]

3. Hide the aggregation table from report view

Power BI automatically routes queries:

• Queries at aggregation grain -> hit the small Import table (fast)
• Queries at detail grain -> hit the DirectQuery detail table (slower but accurate)

Automatic Aggregations (Premium/Fabric)

Premium and Fabric capacities support automatic aggregation training:

• System analyzes query patterns
• Automatically creates and maintains agg tables
• No manual configuration required
• Enable in dataset settings

Composite Models

Mix Import and DirectQuery tables in one model:

Dual mode: Table exists as both Import and DirectQuery. Engine chooses based on query context:

• If all tables in query are Import, uses Import mode (VertiPaq)
• If any table requires DirectQuery, uses DirectQuery for dual tables too

Set storage mode: Model view > select table > Properties > Storage mode

Composite models with Direct Lake (2025 Preview):

• Mix Direct Lake tables with Import tables in a single model
• Direct Lake tables load from OneLake delta files; Import tables from traditional sources
• Enables extending a Fabric lakehouse model with additional reference data
• Monitor fallback behavior -- DL/SQL tables may fall back to DirectQuery under load

Direct Lake Performance

Direct Lake provides near-Import query speed without data duplication:

Large Dataset Optimization (10GB+ Models)

Power BI Desktop Performance Settings

Power BI Report Server Performance Tuning

Report Server has different performance characteristics from the cloud service:

Bookmark and Filter Optimization

DAX Studio Performance Analysis Walkthrough

Installation and Connection

Install

Download from https://daxstudio.org. Free, open-source tool by SQLBI.

Latest Features (2025-2026)

• UDF (user-defined functions) support in code completion and Functions tab
• Custom calendar support in code completion
• Parquet file export for query results
• Support for SSAS 2025
• Totals in Power BI Performance Data view
• RequestID tracing in server timings
• Enhanced tooltip display with event endtime and calculated duration
• Improved clipboard handling and UI enhancements

Connect to Power BI Desktop

1. Open Power BI Desktop with your report 2. Open DAX Studio 3. Select your PBIX file from the connection dialog (auto-detected)

Connect to Power BI Service (XMLA)

1. Requires Premium, PPU, or Fabric capacity 2. Connection string: powerbi://api.powerbi.com/v1.0/myorg/WorkspaceName 3. Initial Catalog: SemanticModelName 4. Authentication: Azure AD (Microsoft Entra ID)

Step 1: Capture the Slow Query

From Performance Analyzer

1. In Power BI Desktop: View > Performance Analyzer > Start recording 2. Click "Refresh visuals" to clear cache 3. Wait for report to render 4. Find the slow visual in the Performance Analyzer pane 5. Click "Copy query" on the slow visual 6. Paste into DAX Studio

Query format from Performance Analyzer

// Query for a bar chart visual
DEFINE
  VAR __DS0Core =
    SUMMARIZECOLUMNS(
      'Product'[Category],
      "SumAmount", CALCULATE(SUM('Sales'[Amount]))
    )
  VAR __DS0PrimaryWindowed =
    TOPN(10, __DS0Core, [SumAmount], DESC)
EVALUATE
  __DS0PrimaryWindowed
ORDER BY [SumAmount] DESC

Step 2: Enable Server Timings

1. Query menu > Server Timings (or Ctrl+Shift+T) 2. This enables detailed engine-level timing

Server Timings Tab Columns

Step 3: Analyze Server Timings

Reading the Results

After executing the query with Server Timings enabled:

Bottom of Server Timings pane shows totals:

• Total: Overall query time
• SE (Storage Engine): Time scanning data
• FE (Formula Engine): Time computing DAX
• SE Queries: Number of SE queries generated
• SE Cache: Number of SE queries served from cache

Interpreting Results

Red Flags in SE Queries

CallbackDataID: When you see CallbackDataID() in an SE query, the Storage Engine is asking the Formula Engine to evaluate an expression for each row. This is extremely slow.

// BAD SE query with CallbackDataID
WITH $Expr0 := (PFCAST([Sales].[Amount] AS INT) + CallbackDataID())
SELECT $Expr0 FROM [Sales]

Fix: Restructure DAX to avoid forcing SE to call back to FE. Common causes:

• Complex expressions in FILTER that cannot be pushed to SE
• Non-supported functions in VertiPaq scan
• Mixing row context and filter context incorrectly

Too many SE queries: Each SE query has overhead. More than 10-15 SE queries for a single visual indicates materialization issues.

Fix: Simplify the DAX, reduce the number of CALCULATE context changes, use variables.

Step 4: VertiPaq Analyzer

Access

Advanced menu > View Metrics (or connect and go to Advanced > View Metrics)

Model Summary

Table Analysis

Sort tables by size (descending) to find the biggest:

Column Analysis (Most Important)

Sort columns by size (descending):

Compression Ratio

Compression Ratio = Data Size / Uncompressed Size

Step 5: Query Plan Analysis

Enable Query Plan

Query menu > Query Plan

Logical Query Plan

Shows what the engine plans to do at a high level:

Physical Query Plan

Shows actual execution:

Red flags in query plans:

• Many SpoolIterator nodes: Indicates excessive materialization
• CrossApply with large cardinality: Nested loops
• No VertiPaq scan operators: Everything in FE, model not being used efficiently

Common Optimization Recipes

Recipe 1: Slow CALCULATE with FILTER

Before (slow):

High Value Sales =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Amount] > 1000)
)

After (fast):

High Value Sales =
CALCULATE(
    SUM(Sales[Amount]),
    Sales[Amount] > 1000
)

Why: Boolean filter generates optimized SE scan. FILTER(Sales,...) forces full table materialization in FE.

Recipe 2: SUMX with RELATED

Before (slow for large tables):

Revenue =
SUMX(Sales, Sales[Quantity] * RELATED(Products[Price]))

After (faster):

-- Add Amount column in Power Query (source-side calculation)
-- Then use simple SUM
Revenue = SUM(Sales[Amount])

Why: Pre-computing in Power Query avoids iterator overhead. If this is not possible, the SUMX version is acceptable but monitor SE timing.

Recipe 3: Counting with conditions

Before (slow):

Active Customers =
COUNTROWS(FILTER(Customers, Customers[Status] = "Active"))

After (fast):

Active Customers =
CALCULATE(COUNTROWS(Customers), Customers[Status] = "Active")

Why: CALCULATE with boolean filter is optimized. FILTER + COUNTROWS materializes the filtered table.

Recipe 4: Multiple IF conditions

Before (slow for many conditions):

Category =
IF([Amount] > 10000, "Premium",
    IF([Amount] > 5000, "Gold",
        IF([Amount] > 1000, "Silver", "Bronze")))

After (marginally faster, more readable):

Category =
SWITCH(TRUE(),
    [Amount] > 10000, "Premium",
    [Amount] > 5000, "Gold",
    [Amount] > 1000, "Silver",
    "Bronze"
)

Recipe 5: Year-over-Year with variables

Before (calculates measure twice):

YoY % =
DIVIDE(
    [Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date])),
    CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date]))
)

After (single PY calculation):

YoY % =
VAR CurrentSales = [Total Sales]
VAR PYSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Date[Date]))
RETURN DIVIDE(CurrentSales - PYSales, PYSales)

Why: Variables are evaluated once and cached. Without VAR, PY calculation runs twice.

Benchmarking Protocol

1. Clear cache: In DAX Studio, clear the SE cache before benchmarking

• Right-click database > Clear Cache (requires admin on XMLA)
• Or disconnect and reconnect

2. Run warm: Execute query once (cold), then again (warm) 3. Compare: Focus on warm timings for user experience 4. Iterate: Make one change at a time, re-measure 5. Document: Record before/after timings for each optimization

VertiPaq Analyzer Updates (2025-2026)

VertiPaq Analyzer v2.1.3 (January 2026):

• Added support for functions metadata
• v2.1.2 (May 2025): Added support for multiple/empty selection DAX expressions in calculation groups
• Fully integrated into DAX Studio Advanced > View Metrics

Using INFO Functions for Model Analysis

As an alternative to VertiPaq Analyzer, use INFO DAX functions directly in DAX query view:

// Get storage statistics for all tables
EVALUATE INFO.STORAGETABLES()

// Get column-level storage details
EVALUATE INFO.STORAGETABLECOLUMNS()

// Get segment-level detail (most granular)
EVALUATE INFO.STORAGETABLECOLUMNSEGMENTS()

These INFO functions return the same underlying data that VertiPaq Analyzer uses but are accessible directly from DAX query view without external tools.

Direct Lake Performance Diagnostics

For Direct Lake models, standard DAX Studio Server Timings still apply. Additional diagnostics:

Optimization recipe for Direct Lake: 1. Enable V-Order writes in Spark notebooks 2. Compact small delta files (OPTIMIZE command) 3. Remove unused columns from gold layer tables 4. Monitor with INFO.STORAGETABLES() for memory footprint 5. Set DirectLakeBehavior = DirectLakeOnly to surface issues early