Performance Tuning: Optimization Techniques for Power BI Models

Performance Tuning: Optimization Techniques for Power BI Models

Introduction

Performance is the difference between a Power BI solution users love and one they avoid. Slow-loading reports, sluggish visuals, and lengthy refresh operations frustrate users and waste resources. This comprehensive guide provides systematic optimization techniques spanning data modeling, DAX efficiency, VertiPaq compression, query patterns, and diagnostic tools.

You'll learn how to diagnose performance bottlenecks, implement proven optimization strategies, and build semantic models that scale from thousands to billions of rows while maintaining sub-second query response times.

Prerequisites

• Power BI Desktop (latest version)
• DAX Studio (free tool for query analysis)
• Tabular Editor 2 or 3 (optional but recommended)
• Understanding of star schema and DAX fundamentals
• Sample dataset with at least 1 million rows for testing

Understanding Power BI Performance Architecture

Query Execution Flow

User Interaction (Visual)
         ↓
DAX Query Generation
         ↓
Formula Engine (FE)
         ├─ Parse DAX expression
         ├─ Create logical query plan
         └─ Optimize execution plan
         ↓
Storage Engine (SE) / VertiPaq
         ├─ Scan compressed columns
         ├─ Apply filters
         ├─ Perform aggregations
         └─ Return results to FE
         ↓
Formula Engine (post-processing)
         ├─ Apply row-context operations
         ├─ Calculate measures
         └─ Format results
         ↓
Visual Rendering

Performance Bottleneck Identification

Layer	Symptoms	Common Causes
Storage Engine	Slow data scans, high memory usage	Large uncompressed columns, missing aggregations, excessive cardinality
Formula Engine	CPU-intensive calculations, slow measure evaluation	Row-context iterations (iterators), complex nested calculations, inefficient filter context
Data Refresh	Long refresh times, memory errors	Non-folding Power Query steps, large full refreshes, inefficient source queries
Network	Slow report loads, timeout errors	Large visual payloads, too many visuals per page, missing caching

Column and Data Type Optimization

Remove Unused Columns Early

// Power Query M: Remove columns immediately after source
let
    Source = Sql.Database("server", "database"),
    SalesTable = Source{[Schema="dbo",Item="FactSales"]}[Data],
    
    // Remove unused columns BEFORE any transformations
    RemoveUnusedColumns = Table.SelectColumns(SalesTable, {
        "OrderID",
        "OrderDate",
        "CustomerID",
        "ProductID",
        "Quantity",
        "UnitPrice"
        // Removed: InternalNotes, CreatedBy, ModifiedDate, etc.
    }),
    
    // Continue with other transformations
    ChangedTypes = Table.TransformColumnTypes(RemoveUnusedColumns, {
        {"OrderDate", type date},
        {"Quantity", Int64.Type},
        {"UnitPrice", type number}
    })
in
    ChangedTypes

Impact: Each removed column reduces model size by its compressed size. Removing 10 text columns from a 10M row table can save 50-200 MB.

Optimize Data Types

// Inefficient: Text keys and flags
CustomerID: type text         // "CUST-000001" (11 bytes × compression)
IsActive: type text           // "Yes"/"No" (3 bytes × compression)
Region: type text             // "North America" (13 bytes × compression)

// Optimized: Numeric keys and Boolean flags
CustomerID: Int64.Type        // 123456 (8 bytes, excellent compression)
IsActive: type logical        // true/false (1 bit)
Region: Int32.Type            // 1 (4 bytes, maps to dimension)

Optimization Script:

// Replace text boolean columns with logical type
let
    Source = Table.FromRows(...),
    
    // Convert text flags to Boolean
    ReplaceYesNo = Table.ReplaceValue(Source, "Yes", true, Replacer.ReplaceValue, {"IsActive"}),
    ReplaceYesNoFalse = Table.ReplaceValue(ReplaceYesNo, "No", false, Replacer.ReplaceValue, {"IsActive"}),
    ChangeToLogical = Table.TransformColumnTypes(ReplaceYesNoFalse, {{"IsActive", type logical}}),
    
    // Replace text IDs with integers (requires mapping table)
    MergeWithDimension = Table.NestedJoin(ChangeToLogical, {"CustomerName"}, CustomerDimension, {"CustomerName"}, "CustomerLookup", JoinKind.LeftOuter),
    ExpandCustomerID = Table.ExpandTableColumn(MergeWithDimension, "CustomerLookup", {"CustomerKey"}, {"CustomerID"}),
    RemoveCustomerName = Table.RemoveColumns(ExpandCustomerID, {"CustomerName"}),
    ChangeIDType = Table.TransformColumnTypes(RemoveCustomerName, {{"CustomerID", Int64.Type}})
in
    ChangeIDType

Cardinality Management

High cardinality columns compress poorly and slow queries. Strategies to reduce cardinality:

// Problem: Email addresses (high cardinality, poor compression)
// 1 million unique emails in 10 million rows

// Solution 1: Remove if not needed for analysis
let
    Source = ...,
    RemoveEmail = Table.RemoveColumns(Source, {"EmailAddress"})
in
    RemoveEmail

// Solution 2: Extract domain if pattern analysis needed
let
    Source = ...,
    ExtractDomain = Table.AddColumn(Source, "EmailDomain", 
        each Text.AfterDelimiter([EmailAddress], "@"), type text),
    RemoveEmail = Table.RemoveColumns(ExtractDomain, {"EmailAddress"})
in
    RemoveEmail
// Result: Reduces ~1M unique values to ~100 unique domains

// Solution 3: Bucket/categorize high-cardinality numeric columns
let
    Source = ...,
    AddAgeGroup = Table.AddColumn(Source, "AgeGroup", 
        each if [Age] < 18 then "Under 18"
             else if [Age] < 30 then "18-29"
             else if [Age] < 50 then "30-49"
             else if [Age] < 65 then "50-64"
             else "65+", type text),
    RemoveAge = Table.RemoveColumns(AddAgeGroup, {"Age"})
in
    RemoveAge
// Result: Reduces continuous values to 5 categories

Calculated Columns vs Measures

Performance Comparison

Aspect	Calculated Column	Measure
Evaluation Time	At refresh (row-context)	At query time (filter context)
Storage	Stored in model, increases size	Not stored, computed on demand
Memory Impact	High (stored for every row)	Low (aggregated results only)
Refresh Impact	Slows refresh	No refresh impact
Use Cases	Static categorization, grouping	Dynamic aggregations, KPIs
Performance	Fast queries (pre-computed)	Fast refresh, slightly slower queries

Converting Calculated Columns to Measures

// ❌ BAD: Calculated Column (stored for every row)
Sales[TotalAmount] = Sales[Quantity] * Sales[UnitPrice]
// 10M rows × 8 bytes = 80 MB added to model

// ✅ GOOD: Measure (computed on demand)
Total Amount = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
// No storage cost, computed at aggregation level

// ❌ BAD: Calculated Column for categorization
Sales[SalesCategory] = 
    IF(Sales[TotalAmount] < 100, "Small",
    IF(Sales[TotalAmount] < 1000, "Medium", "Large"))

// ✅ GOOD: Measure with dynamic categorization
Sales Category = 
VAR TotalSales = [Total Amount]
RETURN
    SWITCH(TRUE(),
        TotalSales < 100, "Small",
        TotalSales < 1000, "Medium",
        "Large"
    )

When Calculated Columns ARE Appropriate:

1. Grouping/categorization used in slicers (requires materialization)
2. Complex text manipulation needed for relationships
3. Row-level calculations required for RLS

// Appropriate use of calculated column: Slicer categorization
Customer[RevenueSegment] = 
    VAR CustomerRevenue = 
        CALCULATE(
            SUM(Sales[Amount]),
            ALLEXCEPT(Customer, Customer[CustomerID])
        )
    RETURN
        SWITCH(TRUE(),
            CustomerRevenue > 100000, "Enterprise",
            CustomerRevenue > 10000, "Business",
            "Consumer"
        )
// Must be stored because used in slicer for filtering

Aggregations: The Performance Multiplier

Understanding Aggregations

Aggregations are pre-summarized tables that Power BI automatically uses to accelerate queries, reducing scan size from billions to millions or thousands of rows.

User Query: Total Sales by Product Category (2023)
         ↓
Power BI Query Engine
         ├─ Checks for matching aggregation
         ↓
Option 1: No Aggregation
         └─ Scans 100M rows in Sales fact table
         └─ Time: 5-10 seconds

Option 2: With Aggregation
         └─ Scans 1K rows in Sales_Agg table
         └─ Time: 0.1 seconds
         └─ 50-100x faster!

Creating Aggregation Tables

// Step 1: Create aggregation table
Sales_Agg_Daily = 
SUMMARIZECOLUMNS(
    'Date'[Date],
    'Product'[CategoryID],
    'Customer'[CountryID],
    'Store'[StoreID],
    "TotalSales", SUM(Sales[Amount]),
    "TotalQuantity", SUM(Sales[Quantity]),
    "OrderCount", COUNTROWS(Sales),
    "AvgOrderValue", AVERAGE(Sales[Amount])
)

// Step 2: Configure aggregation in Model View
// Right-click aggregation table → Manage Aggregations
// Map columns:
// Sales_Agg_Daily[Date] → Sales[OrderDate] (GroupBy)
// Sales_Agg_Daily[CategoryID] → Product[CategoryID] (GroupBy)
// Sales_Agg_Daily[TotalSales] → Sales[Amount] (Sum)
// Sales_Agg_Daily[TotalQuantity] → Sales[Quantity] (Sum)
// Sales_Agg_Daily[OrderCount] → Sales[OrderID] (Count)

Aggregation Best Practices

// Multiple aggregation levels for different query patterns

// Aggregation Level 1: Daily (finest grain)
Sales_Agg_Daily = 
SUMMARIZECOLUMNS(
    'Date'[Date],
    'Product'[ProductID],
    'Customer'[CustomerID],
    "TotalSales", SUM(Sales[Amount]),
    "OrderCount", COUNTROWS(Sales)
)
// Use case: Detailed daily analysis by product/customer
// Compression: 100M rows → 10M rows (10x)

// Aggregation Level 2: Monthly by Category
Sales_Agg_Monthly = 
SUMMARIZECOLUMNS(
    'Date'[Year],
    'Date'[MonthNumber],
    'Product'[CategoryID],
    'Customer'[CountryID],
    "TotalSales", SUM(Sales[Amount]),
    "OrderCount", COUNTROWS(Sales)
)
// Use case: Monthly reporting by category
// Compression: 100M rows → 50K rows (2000x)

// Aggregation Level 3: Yearly Summary
Sales_Agg_Yearly = 
SUMMARIZECOLUMNS(
    'Date'[Year],
    'Product'[CategoryID],
    "TotalSales", SUM(Sales[Amount]),
    "OrderCount", COUNTROWS(Sales)
)
// Use case: Year-over-year trending
// Compression: 100M rows → 500 rows (200,000x)

Aggregation Troubleshooting

// Check if aggregations are being used
// In DAX Studio, run query and check Query Plan

// Query that SHOULD use aggregation:
EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Year],
    'Product'[Category],
    "Total Sales", [Total Amount]
)

// Look for "VertiPaq Scan" in query plan:
// ✅ Scanning Sales_Agg_Yearly (500 rows)
// ❌ Scanning Sales (100M rows) → aggregation not used

// Common reasons aggregations aren't used:
// 1. Query includes dimension attributes not in aggregation
// 2. Using calculated columns in query (not mapped in aggregation)
// 3. RLS applied (aggregations respect RLS, may bypass if filters don't align)
// 4. Aggregation table hidden but not marked as aggregation

DAX Optimization Patterns

Use Variables to Avoid Recalculation

// ❌ SLOW: Multiple evaluations of same expression
Total Profit Margin = 
DIVIDE(
    SUM(Sales[Amount]) - SUM(Sales[Cost]),
    SUM(Sales[Amount]),
    0
)
// SUM(Sales[Amount]) calculated twice!

// ✅ FAST: Calculate once with variable
Total Profit Margin = 
VAR TotalSales = SUM(Sales[Amount])
VAR TotalCost = SUM(Sales[Cost])
VAR Profit = TotalSales - TotalCost
RETURN
    DIVIDE(Profit, TotalSales, 0)

Avoid Expensive Iterators

// ❌ VERY SLOW: Row-context iteration over millions of rows
Total Sales Slow = 
SUMX(
    Sales,
    IF(
        Sales[Quantity] > 10,
        Sales[Amount] * 1.1,
        Sales[Amount]
    )
)
// Iterates every row individually in Formula Engine

// ✅ FAST: Filter-context aggregation in Storage Engine
Total Sales Fast = 
    CALCULATE(SUM(Sales[Amount]), Sales[Quantity] > 10) * 1.1
    + CALCULATE(SUM(Sales[Amount]), Sales[Quantity] <= 10)
// Aggregates in VertiPaq, 100x faster

// ✅ ALTERNATIVE: Pre-compute in calculated column (if needed frequently)
Sales[AdjustedAmount] = IF(Sales[Quantity] > 10, Sales[Amount] * 1.1, Sales[Amount])

Total Sales Fast = SUM(Sales[AdjustedAmount])

Replace Nested IF with SWITCH(TRUE())

// ❌ HARD TO READ AND MAINTAIN
Customer Segment = 
IF([Total Revenue] > 100000, "Platinum",
    IF([Total Revenue] > 50000, "Gold",
        IF([Total Revenue] > 10000, "Silver",
            "Bronze"
        )
    )
)

// ✅ CLEAN AND PERFORMANT
Customer Segment = 
SWITCH(TRUE(),
    [Total Revenue] > 100000, "Platinum",
    [Total Revenue] > 50000, "Gold",
    [Total Revenue] > 10000, "Silver",
    "Bronze"
)

Optimize Time Intelligence

// ❌ SLOW: Dynamic date filtering with FILTER
YTD Sales Slow = 
CALCULATE(
    [Total Amount],
    FILTER(
        ALL('Date'),
        'Date'[Date] >= DATE(YEAR(MAX('Date'[Date])), 1, 1) &&
        'Date'[Date] <= MAX('Date'[Date])
    )
)

// ✅ FAST: Use built-in time intelligence functions
YTD Sales Fast = 
CALCULATE(
    [Total Amount],
    DATESYTD('Date'[Date])
)

// Even better: Pre-calculate in date dimension
// Date[IsCurrentYear] = YEAR('Date'[Date]) = YEAR(TODAY())
YTD Sales Fastest = 
CALCULATE(
    [Total Amount],
    'Date'[IsCurrentYear] = TRUE,
    'Date'[Date] <= MAX('Date'[Date])
)

Minimize Context Transitions

// ❌ SLOW: Multiple context transitions
Sales per Customer = 
AVERAGEX(
    Customer,
    CALCULATE(SUM(Sales[Amount]))  // Context transition for each customer
)

// ✅ FAST: Single aggregation
Sales per Customer = 
DIVIDE(
    SUM(Sales[Amount]),
    DISTINCTCOUNT(Sales[CustomerID]),
    0
)

VertiPaq Compression Optimization

Understanding VertiPaq Storage

Column Storage in VertiPaq:

1. Dictionary Encoding
   Original: ["Red", "Blue", "Red", "Green", "Blue", "Red"]
   Dictionary: {0:"Red", 1:"Blue", 2:"Green"}
   Encoded: [0, 1, 0, 2, 1, 0]
   
2. Value Encoding
   Finds repeated patterns and compresses
   
3. Run-Length Encoding (RLE)
   Consecutive identical values compressed
   Sorted columns compress better!

Compression Ratio Examples:
- Integer column (sorted): 90-95% compression
- Text column (high cardinality): 50-70% compression
- Date column: 85-95% compression
- Boolean column: 99% compression

Analyzing Compression with VertiPaq Analyzer

// Install VertiPaq Analyzer for DAX Studio
// Connect DAX Studio to dataset via XMLA endpoint

// Run VertiPaq Analyzer scan
// Review key metrics:

Table: Sales (100M rows)
├─ Total Size: 2.5 GB
├─ Compression: 85% (uncompressed: 16.7 GB)
│
├─ Column: CustomerID (Int64, 10K unique)
│   ├─ Size: 50 MB
│   ├─ Compression: 95%
│   └─ ✅ Excellent compression
│
├─ Column: CustomerName (Text, 10K unique)
│   ├─ Size: 180 MB
│   ├─ Compression: 70%
│   └─ ⚠️  Consider removing (use ID + dimension)
│
├─ Column: OrderNotes (Text, 8M unique)
│   ├─ Size: 1.2 GB
│   ├─ Compression: 30%
│   └─ ❌ Remove or move to separate table

Action Items:
1. Remove OrderNotes column → Save 1.2 GB
2. Remove CustomerName (redundant) → Save 180 MB
3. Sort CustomerID for better compression

Sort Columns for Better Compression

// Power Query: Sort fact table by dimension keys
let
    Source = ...,
    
    // Sort by most important dimension (typically date)
    SortedRows = Table.Sort(Source, {
        {"OrderDate", Order.Ascending},
        {"CustomerID", Order.Ascending},
        {"ProductID", Order.Ascending}
    }),
    
    // Apply other transformations...
in
    SortedRows

// Impact: 10-20% better compression due to RLE optimization

Hash Columns for High-Cardinality

// Instead of storing full transaction ID:
// TransactionID: "TXN-2023-0001-A1B2C3D4" (20 characters)

// Store hash instead:
Sales[TransactionHash] = 
    HASHBYTES("MD5", Sales[TransactionID])
// Result: 16-byte binary, excellent compression
// Use only if original value not needed for display

Relationship Optimization

Single-Direction vs Bi-Directional Filters

Single-Direction (Default - Recommended):

Dimension → Fact
  Date     →  Sales
Product    →  Sales
Customer   →  Sales

✅ Pros:
- Predictable behavior
- Better performance
- Easier to troubleshoot
- No ambiguity in filter propagation

❌ Cons:
- Cannot filter dimension from fact

Bi-Directional (Use Sparingly):

Dimension ↔ Fact
  Date    ↔  Sales

✅ Use cases:
- Many-to-many relationships
- Role-playing dimensions (OrderDate, ShipDate)
- Bridge tables in many-to-many

❌ Cons:
- Can create unexpected results
- Slower performance
- Risk of circular dependencies
- Harder to debug

Optimizing Many-to-Many Relationships

// Scenario: Students enrolled in multiple Courses
// Bridge table: Enrollments

// ❌ SLOW: Virtual many-to-many
Total Students = 
CALCULATE(
    DISTINCTCOUNT(Enrollments[StudentID]),
    USERELATIONSHIP(Courses[CourseID], Enrollments[CourseID])
)

// ✅ BETTER: Materialized bridge pattern
// Create optimized bridge table with aggregations
EnrollmentsBridge = 
SUMMARIZE(
    Enrollments,
    Enrollments[StudentID],
    Enrollments[CourseID]
)

// Use relationships:
// Students (1) → EnrollmentsBridge (*) ← (*) Courses (1)

// Much faster filtering and counting

Inactive Relationships Strategy

// Multiple date relationships: OrderDate, ShipDate, DeliveryDate

// Mark only OrderDate as active
// Use inactive relationships explicitly in measures

Sales by Order Date = 
CALCULATE(
    SUM(Sales[Amount])
    // Uses active relationship automatically
)

Sales by Ship Date = 
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])
)

Sales by Delivery Date = 
CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[DeliveryDate], 'Date'[Date])
)

// Avoids need for bi-directional or many-to-many relationships

Incremental Refresh Configuration

Setting Up Incremental Refresh

// Step 1: Create RangeStart and RangeEnd parameters in Power Query
// RangeStart: #datetime(2020, 1, 1, 0, 0, 0) (type: DateTime)
// RangeEnd: #datetime(2025, 12, 31, 23, 59, 59) (type: DateTime)

// Step 2: Filter fact table using parameters
let
    Source = Sql.Database("server", "database"),
    Sales = Source{[Schema="dbo",Item="FactSales"]}[Data],
    
    // CRITICAL: Filter using parameters
    FilteredRows = Table.SelectRows(Sales, 
        each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd),
    
    // Other transformations...
in
    FilteredRows

// Step 3: Configure incremental refresh policy
// In Power BI Desktop:
// 1. Right-click table → Incremental refresh
// 2. Archive data: Keep 5 years
// 3. Incrementally refresh: Last 7 days
// 4. Detect data changes: OrderDate (optional)
// 5. Only refresh complete days: Yes

Incremental Refresh Benefits:

• Refresh time reduced from hours to minutes
• Memory usage reduced during refresh
• Historical data partitioned and compressed
• Only recent data refreshed regularly

Without Incremental Refresh:
┌────────────────────────────────────┐
│      Full Dataset (5 years)        │
│      500M rows refreshed daily     │
│      Refresh time: 4 hours         │
│      Memory: 32 GB peak            │
└────────────────────────────────────┘

With Incremental Refresh:
┌────────────────────────────────────┐
│ Historical (4 years, 11 months)    │
│ 480M rows → Never refreshed        │
│ Compressed partitions              │
├────────────────────────────────────┤
│ Recent (7 days)                    │
│ 20M rows → Refreshed daily         │
│ Refresh time: 15 minutes           │
│ Memory: 4 GB peak                  │
└────────────────────────────────────┘

Query Diagnostics and Troubleshooting

Using Performance Analyzer

Power BI Desktop → View → Performance Analyzer

Steps:
1. Click "Start recording"
2. Interact with report (click slicer, change filter)
3. Click "Stop recording"
4. Review timing breakdown:

Example Report Page Load:
├─ DAX query: 1,250 ms
│   ├─ Visual 1 (Column chart): 450 ms
│   ├─ Visual 2 (Table): 650 ms
│   └─ Visual 3 (Card): 150 ms
├─ Visual display: 180 ms
└─ Other: 70 ms
Total: 1,500 ms

Action Items:
- Visual 2 (Table) is slowest → Investigate DAX
- Copy query and analyze in DAX Studio

DAX Studio Query Analysis

// Paste query from Performance Analyzer into DAX Studio
// Run with "Server Timings" enabled

DEFINE
VAR __DS0FilterTable = 
    TREATAS({"Electronics"}, 'Product'[Category])

EVALUATE
SUMMARIZECOLUMNS(
    ROLLUPADDISSUBTOTAL('Date'[Year], "IsGrandTotalRowTotal"),
    __DS0FilterTable,
    "TotalSales", [Total Amount]
)

// Review Server Timings tab:
// Storage Engine CPU: 45 ms  (data scan)
// Formula Engine CPU: 605 ms  (calculations) ← Bottleneck!
// Total Duration: 650 ms

// High Formula Engine time indicates:
// 1. Row-context iterations
// 2. Complex calculated columns
// 3. Inefficient measures

// Refactor [Total Amount] measure to reduce FE time

Common Query Patterns to Avoid

// ❌ Anti-Pattern 1: Scanning unrelated tables
Slow Measure = 
SUMX(
    ALL(DimProduct),  // Scans entire product table unnecessarily
    [Total Amount]
)

// ✅ Pattern: Only reference related tables
Fast Measure = [Total Amount]

// ❌ Anti-Pattern 2: Excessive CALCULATE nesting
Slow Nested = 
CALCULATE(
    CALCULATE(
        CALCULATE(
            [Total Amount],
            Filter1
        ),
        Filter2
    ),
    Filter3
)

// ✅ Pattern: Single CALCULATE with multiple filters
Fast Flat = 
CALCULATE(
    [Total Amount],
    Filter1,
    Filter2,
    Filter3
)

// ❌ Anti-Pattern 3: FILTER on large tables
Slow Filter = 
CALCULATE(
    [Total Amount],
    FILTER(Sales, Sales[Amount] > 100)
)

// ✅ Pattern: Use table filter directly
Fast Filter = 
CALCULATE(
    [Total Amount],
    Sales[Amount] > 100
)

Power Query Optimization

Query Folding Best Practices

// ✅ GOOD: Steps that fold to database (pushed to SQL Server)
let
    Source = Sql.Database("server", "database"),
    Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    FilteredRows = Table.SelectRows(Sales, each [OrderDate] >= #date(2023,1,1)),  // ✅ Folds
    RemovedColumns = Table.RemoveColumns(FilteredRows, {"InternalNotes"}),        // ✅ Folds
    ChangedType = Table.TransformColumnTypes(RemovedColumns, {{"Amount", type number}}),  // ✅ Folds
    Sorted = Table.Sort(ChangedType, {{"OrderDate", Order.Ascending}})            // ✅ Folds
in
    Sorted
// All operations translated to SQL SELECT query

// ❌ BAD: Steps that break query folding
let
    Source = Sql.Database("server", "database"),
    Sales = Source{[Schema="dbo",Item="Sales"]}[Data],
    
    // ❌ Text.Upper breaks folding (not available in SQL)
    UpperCase = Table.TransformColumns(Sales, {{"CustomerName", Text.Upper}}),
    
    // Everything after this loads to Power BI memory!
    FilteredRows = Table.SelectRows(UpperCase, each [OrderDate] >= #date(2023,1,1)),  // ❌ Doesn't fold
    // 100M rows loaded into memory instead of 10M
in
    FilteredRows

Check Query Folding:
Right-click step → View Native Query

• If available → Step folds ✅
• If greyed out → Folding broken ❌

Optimize M Code

// ❌ SLOW: Multiple iterations
let
    Source = ...,
    Step1 = Table.TransformColumns(Source, {{"Col1", each _ * 1.1}}),
    Step2 = Table.TransformColumns(Step1, {{"Col2", each _ * 0.9}}),
    Step3 = Table.TransformColumns(Step2, {{"Col3", each Text.Upper(_)}})
in
    Step3
// Iterates table 3 times

// ✅ FAST: Single iteration
let
    Source = ...,
    TransformAll = Table.TransformColumns(Source, {
        {"Col1", each _ * 1.1},
        {"Col2", each _ * 0.9},
        {"Col3", each Text.Upper(_)}
    })
in
    TransformAll
// Iterates table once

Report-Level Optimization

Reduce Visual Count

Recommended Visual Limits per Page:

Interactive Reports:
- Maximum: 15-20 visuals
- Optimal: 8-12 visuals
- Critical: Avoid >25 visuals

Dashboards (read-only):
- Maximum: 25-30 visuals
- Optimal: 15-20 visuals

Each visual generates separate DAX query
More visuals = More queries = Slower load

Use Bookmarks for Complex Reports

Instead of cramming everything on one page:

Page 1: Overview
├─ 6 KPI cards
├─ 2 trend charts
└─ 1 summary table

Bookmark "Sales Detail":
├─ Hides overview visuals
├─ Shows detailed breakdown table
└─ Shows drill-through filters

Bookmark "Geographic Analysis":
├─ Shows map visual
├─ Shows region slicer
└─ Hides other visuals

Result: Only active visuals query data

Persistent Filters

// Use persistent filters for common scenarios

// Filter to current year by default (no slicer needed)
Default Year Filter = 
YEAR(TODAY())

// Apply in visual filters or report filter
// Users can override via slicer if needed

Best Practices Checklist

Data Model Optimization

☑ Remove all unused columns (check with View → Column Tools → Summarization)
☑ Replace text keys with integer keys where possible
☑ Convert text booleans to logical type
☑ Reduce high-cardinality columns (bucket/group)
☑ Sort fact tables by main dimension key
☑ Implement star schema (no snowflaking in Power BI)
☑ Use surrogate keys for dimension relationships
☑ Mark date table with "Mark as Date Table"
☑ Disable auto date/time in Options → Data Load
☑ Set proper data categories (URL, Image URL, Address, etc.)

DAX Optimization

☑ Use measures instead of calculated columns when possible
☑ Use variables to avoid recalculation
☑ Replace iterators with aggregation functions where possible
☑ Use SWITCH(TRUE()) instead of nested IF
☑ Use built-in time intelligence functions
☑ Avoid FILTER on large tables (use direct column filters)
☑ Minimize context transitions
☑ Pre-calculate complex logic in Power Query when static
☑ Use SELECTEDVALUE instead of VALUES/HASONEVALUE combination
☑ Test measures with large data volumes

Refresh Optimization

☑ Enable incremental refresh for fact tables >1M rows
☑ Ensure query folding for all large tables
☑ Partition queries to parallelize loads
☑ Schedule refreshes during off-peak hours
☑ Use SQL views instead of complex Power Query transformations
☑ Create indexed views in SQL for better performance
☑ Monitor refresh duration trends (set alerts for anomalies)
☑ Implement refresh failure notifications

Aggregations

☑ Create daily aggregation for fact tables >10M rows
☑ Create monthly aggregation for fact tables >50M rows
☑ Configure aggregation mappings correctly
☑ Test aggregation usage with DAX Studio
☑ Hide aggregation tables from report view
☑ Document aggregation strategy for team

Troubleshooting Performance Issues

Diagnostic Workflow

1. Identify Symptom
   ├─ Slow report load? → Check visual count and query duration
   ├─ Slow refresh? → Check Power Query folding and incremental refresh
   ├─ Large file size? → Check column cardinality and data types
   └─ High memory usage? → Check calculated columns and relationships

2. Measure Baseline
   ├─ Use Performance Analyzer for report timing
   ├─ Use DAX Studio for query analysis
   ├─ Use VertiPaq Analyzer for model size
   └─ Document current metrics

3. Apply Optimizations
   ├─ Start with highest impact (aggregations, column removal)
   ├─ Measure after each change
   └─ Document improvements

4. Validate Results
   ├─ Test with production data volumes
   ├─ Test with concurrent users
   └─ Monitor for regressions

Performance Testing Script

# Automated performance testing for Power BI reports
# Requires Power BI REST API access

$reportId = "your-report-id"
$workspaceId = "your-workspace-id"

# Warm up cache
Invoke-RestMethod -Uri "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/reports/$reportId" -Method Get

# Test query performance
$testQueries = @(
    "EVALUATE TOPN(100, Sales)",
    "EVALUATE SUMMARIZECOLUMNS('Date'[Year], ""Total"", [Total Amount])",
    "EVALUATE FILTER(Sales, Sales[Amount] > 1000)"
)

$results = foreach ($query in $testQueries) {
    $start = Get-Date
    # Execute query via XMLA endpoint
    $result = Invoke-DaxQuery -Query $query -Dataset $reportId
    $duration = (Get-Date) - $start
    
    [PSCustomObject]@{
        Query = $query.Substring(0, 50)
        Duration = $duration.TotalMilliseconds
        RowCount = $result.Count
    }
}

$results | Format-Table -AutoSize

Performance Optimization Workflow Summary

Systematic approach to optimize any Power BI model:

1. Diagnose: Run VertiPaq Analyzer to identify largest tables/columns
2. Model: Remove unused columns, optimize data types, implement aggregations
3. DAX: Profile with DAX Studio, eliminate iterators, use variables
4. Refresh: Enable incremental refresh for large fact tables
5. Monitor: Establish baseline metrics, test after each change
6. Iterate: Performance optimization is continuous, not one-time

Quick Wins:

• Remove unused columns (10-50% size reduction)
• Convert calculated columns to measures (5-30% reduction)
• Add aggregations to DirectQuery facts (100x query speedup)
• Enable query folding in Power Query (10-100x refresh speedup)

Key Takeaways

• Column optimization is the foundation: Remove unused columns, optimize data types, reduce cardinality
• Aggregations provide 10-100x query performance improvements for large datasets
• DAX efficiency matters: Use variables, avoid iterators, leverage filter context over row context
• Incremental refresh is essential for fact tables with millions of rows
• Query folding in Power Query prevents unnecessary data loading
• VertiPaq compression works best with low cardinality, sorted columns
• Relationship design affects query performance: prefer single-direction, use inactive relationships for multiple date scenarios
• Monitoring is continuous: Use Performance Analyzer, DAX Studio, and VertiPaq Analyzer regularly

Next Steps

1. Run VertiPaq Analyzer on your largest model to identify optimization opportunities
2. Implement aggregations for your largest fact tables (>10M rows)
3. Audit all calculated columns and convert to measures where possible
4. Enable incremental refresh for historical fact tables
5. Review DAX measures in DAX Studio and optimize high-FE-time queries
6. Establish performance testing baseline and regression testing process
7. Document optimization standards for your team
8. Schedule quarterly model optimization reviews

Additional Resources

• Power BI Optimization Guide
• DAX Patterns
• SQLBI Optimization Articles
• DAX Studio Documentation
• VertiPaq Analyzer Guide
• Power BI Performance Best Practices

---

Optimize. Measure. Iterate. Scale.