Performance Tuning: Optimization Techniques for Power BI

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

```powerquery
// 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
```sql
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
```text
ChangeIDType

### Cardinality Management

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

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

// Solution 1: Remove if not needed for analysis
let
```text
Source = ...,
RemoveEmail = Table.RemoveColumns(Source, {"EmailAddress"})```
in
```text
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
```text
RemoveEmail```
// Result: Reduces ~1M unique values to ~100 unique domains

// Solution 3: Bucket/categorize high-cardinality numeric columns
let
```text
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
```text
RemoveAge```
// Result: Reduces continuous values to 5 categories

Calculated Columns vs Measures

Performance Comparison

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] = 
```text
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


```dax
// Appropriate use of calculated column: Slicer categorization
Customer[RevenueSegment] = 
```text
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.

Architecture Overview: User Query: Total Sales by Product Category (2023)

Creating Aggregation Tables

// Step 1: Create aggregation table
Sales_Agg_Daily = 
SUMMARIZECOLUMNS(
```text
'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(

> **Architecture Overview:** 'Date'[Date],

'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(

> **Architecture Overview:** 'Date'[Year],


### Aggregation Troubleshooting

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

// Query that SHOULD use aggregation:
EVALUATE
SUMMARIZECOLUMNS(
```text
'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(
```text
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
```text
DIVIDE(Profit, TotalSales, 0)

### Avoid Expensive Iterators

```dax
// ❌ VERY SLOW: Row-context iteration over millions of rows
Total Sales Slow = 
SUMX(
```text
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 = 
```text
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",
```text
IF([Total Revenue] > 50000, "Gold",
    IF([Total Revenue] > 10000, "Silver",
        "Bronze"
    )
)```
)

// ✅ CLEAN AND PERFORMANT
Customer Segment = 
SWITCH(TRUE(),
```text
[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(
```text
[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(
```text
[Total Amount],
DATESYTD('Date'[Date])```
)

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

Minimize Context Transitions

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

// ✅ FAST: Single aggregation
Sales per Customer = 
DIVIDE(
```text
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

Diagram: See the official Microsoft documentation for architecture details.

Sort Columns for Better Compression

// Power Query: Sort fact table by dimension keys
let
```text
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
```text
SortedRows

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

### Hash Columns for High-Cardinality

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

// Store hash instead:
Sales[TransactionHash] = 
```text
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(
```text
DISTINCTCOUNT(Enrollments[StudentID]),
USERELATIONSHIP(Courses[CourseID], Enrollments[CourseID])```
)

// ✅ BETTER: Materialized bridge pattern
// Create optimized bridge table with aggregations
EnrollmentsBridge = 
SUMMARIZE(
```text
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(
```text
SUM(Sales[Amount])
// Uses active relationship automatically```
)

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

Sales by Delivery Date = 
CALCULATE(
```text
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
```csharp
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
```text
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

Diagram: See the official Microsoft documentation for architecture details.

Query Diagnostics and Troubleshooting

Using Performance Analyzer

Diagram: See the official Microsoft documentation for architecture details.

DAX Studio Query Analysis

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

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

EVALUATE SUMMARIZECOLUMNS(

Architecture Overview: ROLLUPADDISSUBTOTAL('Date'[Year], "IsGrandTotalRowTotal"),

Common Query Patterns to Avoid

// ❌ Anti-Pattern 1: Scanning unrelated tables
Slow Measure = 
SUMX(
```text
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(
```text
CALCULATE(
    CALCULATE(
        [Total Amount],
        Filter1
    ),
    Filter2
),
Filter3```
)

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

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

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

Power Query Optimization

Query Folding Best Practices

// ✅ GOOD: Steps that fold to database (pushed to SQL Server)
let
```text
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
```text
Sorted```
// All operations translated to SQL SELECT query

// ❌ BAD: Steps that break query folding
let
```text
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
```text
FilteredRows

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

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


### Optimize M Code

```powerquery
// ❌ SLOW: Multiple iterations
let
```text
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
```text
Step3```
// Iterates table 3 times

// ✅ FAST: Single iteration
let
```text
Source = ...,
TransformAll = Table.TransformColumns(Source, {
    {"Col1", each _ * 1.1},
    {"Col2", each _ * 0.9},
    {"Col3", each Text.Upper(_)}
})```
in
```text
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

Architecture Overview: Instead of cramming everything on one page:

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

Architecture Overview: ☑ Remove all unused columns (check with View → Column Tools → Summarization)

DAX Optimization

Architecture Overview: ☑ Use measures instead of calculated columns when possible

Refresh Optimization

Architecture Overview: ☑ Enable incremental refresh for fact tables >1M rows

Aggregations

Architecture Overview: ☑ Create daily aggregation for fact tables >10M rows

Troubleshooting Performance Issues

Diagnostic Workflow

Architecture Overview: 1. Identify Symptom

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 = @(
```text
"EVALUATE TOPN(100, Sales)",
"EVALUATE SUMMARIZECOLUMNS('Date'[Year], ""Total"", [Total Amount])",
"EVALUATE FILTER(Sales, Sales[Amount] > 1000)"```
)





$results = foreach ($query in $testQueries) {
```powershell
$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)

Architecture Decision and Tradeoffs

When designing business intelligence solutions with Power BI, consider these key architectural trade-offs:

Recommendation: Start with the managed approach for most workloads and move to custom only when specific requirements demand it.

Validation and Versioning

• Last validated: April 2026
• Validate examples against your tenant, region, and SKU constraints before production rollout.
• Keep module, CLI, and SDK versions pinned in automation pipelines and review quarterly.

Security and Governance Considerations

• Apply least-privilege access using RBAC roles and just-in-time elevation for admin tasks.
• Store secrets in managed secret stores and avoid embedding credentials in scripts or source files.
• Enable audit logging, data protection policies, and periodic access reviews for regulated workloads.

Cost and Performance Notes

• Define budgets and alerts, then monitor usage and cost trends continuously after go-live.
• Baseline performance with synthetic and real-user checks before and after major changes.
• Scale resources with measured thresholds and revisit sizing after usage pattern changes.

Official Microsoft References

• https://learn.microsoft.com/power-bi/
• https://learn.microsoft.com/power-bi/guidance/
• https://learn.microsoft.com/fabric/

Public Examples from Official Sources

• These examples are sourced from official public Microsoft documentation and sample repositories.
• Documentation examples: https://learn.microsoft.com/power-bi/
• Sample repositories: https://github.com/microsoft/PowerBI-Developer-Samples
• Prefer adapting these examples to your tenant, subscriptions, and governance requirements before production use.

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.