Performance Optimization

What You'll Learn

• Performance bottlenecks
• Optimization techniques
• Data model best practices
• Query optimization

Common Performance Issues

Slow loading:

• Too much data
• Complex DAX
• Many visuals
• Inefficient model

Slow interactions:

• Poor relationships
• Calculated columns on large tables
• Cross-filtering too many visuals

Data Model Optimization

Reduce Data Volume

Load only what you need:

• Filter in Power Query
• Remove unused columns
• Limit date range
• Aggregate in source

Example: Instead of 5 years daily, use monthly aggregates

Use Star Schema

Fact table: Transactions Dimension tables: Attributes

Better than: One huge flat table

Remove Calculated Columns

Replace with:

• Power Query columns (better)
• Measures (when possible)

Calculated columns:

• Increase model size
• Slow refresh
• Use memory

Optimize Data Types

Use smallest type:

• Integer instead of Decimal
• Date instead of DateTime
• Remove precision you don't need

Savings: Int = 4 bytes Decimal = 8 bytes 2x smaller!

DAX Optimization

Use Variables

Instead of: Measure = [Total Sales] / [Total Sales] * 100

Use: Measure = VAR Total = [Total Sales] RETURN Total / Total * 100

Calculates once!

Avoid Iterators

Slow: SUMX(Sales, Sales[Qty] * Sales[Price])

Faster (if column exists): SUM(Sales[Amount])

Create Amount in Power Query!

Use DIVIDE

Handles errors: DIVIDE([A], [B])

Better than: IF([B] = 0, BLANK(), [A] / [B])

Visual Optimization

Limit Visuals Per Page

Maximum 7-10 visuals

More = slower

Reduce Data Points

Aggregate:

• Monthly instead of daily
• Top 10 instead of all
• Summary instead of detail

Use Buttons Over Visuals

For navigation: Buttons load faster than visuals

Relationship Optimization

Avoid Bidirectional

Use single direction: Faster and more predictable

Bidirectional only when: Absolutely necessary

Minimize Relationships

Only create what you need

Don't connect everything!

Import vs DirectQuery

Import Mode

Fast: Data in memory

Use for:

• Small/medium datasets
• Need all features
• Performance critical

DirectQuery

Real-time: Queries source each time

Use for:

• Very large data
• Real-time required
• Data stays in source

Aggregations

Best of both:

• Summary in Import (fast)
• Detail in DirectQuery (fresh)

Monitoring Performance

Performance Analyzer

View > Performance Analyzer

Shows:

• Visual load time
• DAX query time
• Visual display time

Use to:

• Find slow visuals
• Identify bottlenecks
• Test optimizations

DAX Studio

External tool:

• Analyze queries
• Server timings
• Query plans

Advanced!

Quick Wins

Easy optimizations:

1. Remove unused visuals
2. Reduce date range
3. Use slicers to filter
4. Aggregate in Power Query
5. Remove calculated columns
6. Disable cross-filtering where not needed
7. Use Import over DirectQuery

Try This Exercise

Optimize a report:

1. Open Performance Analyzer
2. Refresh visuals
3. Find slowest visual
4. Reduce data (Top N filter)
5. Re-test
6. Note improvement!

Best Practices

Model size: Keep under 1 GB when possible

Visual count: 5-7 per page

Data refresh: Only refresh when needed

Incremental refresh: For large datasets

Next Steps

Learn about Publishing & Sharing your reports!

Tip: 80% of performance = good data model. Fix model first!