Performance Optimization

A fast report isn't just better UX — it's cheaper to run. Every query consumes CUs from your Fabric capacity. Optimize once, save capacity forever.

Optimization Priority Order

Fix these in order — each builds on the previous:

1. Reduce visual count (biggest impact, easiest fix)
2. Optimize DAX measures (medium effort, high impact)
3. Hide unused columns (easy, DirectLake-specific)
4. Ensure query folding (Power Query, one-time fix)
5. Tune refresh scheduling (operational, ongoing)

1. Reduce Visual Count

Every visual on a page generates at least one query. A page with 30 visuals generates 30+ queries on every page load and every slicer change.

Fixes:

• Combine visuals: Two cards showing "Total" and "Average" can be one card with a visual toggle
• Use bookmarks for views: Instead of showing everything at once, use bookmarks to switch between views
• Move detail to drillthrough: Keep the summary page light; put detailed tables on a drillthrough page that loads on demand
• Remove decorative visuals: Shapes, images, and text boxes that don't show data still contribute to render time

2. Optimize DAX Measures

Use Variables (VAR/RETURN)

-- BAD: CALCULATE runs twice
YoY Change = 
    [Total Storage GB] - 
    CALCULATE([Total Storage GB], SAMEPERIODLASTYEAR(Date[Date]))

-- GOOD: Each value computed once
YoY Change = 
VAR Current = [Total Storage GB]
VAR PriorYear = CALCULATE([Total Storage GB], SAMEPERIODLASTYEAR(Date[Date]))
RETURN Current - PriorYear

Avoid FILTER(ALL(...)) — Use REMOVEFILTERS

-- BAD: Materializes entire table, then filters
% of Total = DIVIDE(
    [Total Storage GB],
    CALCULATE([Total Storage GB], FILTER(ALL(Person), TRUE()))
)

-- GOOD: Removes filter without materializing
% of Total = DIVIDE(
    [Total Storage GB],
    CALCULATE([Total Storage GB], REMOVEFILTERS(Person[Department]))
)

Avoid Iterators on Large Tables

SUMX, AVERAGEX, MAXX iterate row-by-row. On a 10-million-row table, that's 10 million iterations per visual per query.

-- BAD: Row-by-row iteration
Total GB = SUMX(EXO_Storage, EXO_Storage[Storage_Used_Bytes] / 1073741824)

-- GOOD: Aggregate first, then convert
Total GB = DIVIDE(SUM(EXO_Storage[Storage_Used_Bytes]), 1073741824)

3. Hide Unused Columns (DirectLake)

DirectLake loads all columns in every referenced table into the VertiPaq engine. If your EXO_Storage table has 50 columns but your report uses 8, the other 42 columns are loaded for nothing.

Fix:

1. In Model view, right-click unused columns → Hide in report view
2. Better: In the semantic model (TMDL), set isHidden: true on unused columns
3. Best: Create a Silver/Gold view that only includes the columns you need

Impact: Reducing from 50 to 8 columns can cut model size by 80%, keeping you within your DirectLake tier limit.

4. Ensure Query Folding (Power Query)

Query folding means Power Query translates your M transformations into SQL and pushes them to the source. If folding breaks, Power Query downloads the entire table into memory and transforms locally — which is vastly slower and more expensive.

How to Check

In Power Query Editor:

1. Right-click a step → View Native Query
2. If you see SQL → folding is working
3. If it says "This step can't be folded" → you broke folding at this step

What Breaks Folding

The Rule

Do as much as possible in steps that fold. Move non-folding operations to the end of the query chain. Once folding breaks at a step, every step after it also doesn't fold.

5. Tune Refresh Scheduling

See Database Decision Matrix: Refresh Architecture for detailed scheduling guidance.

Key points:

• Stagger refreshes by 15–30 minutes
• Run heavy refreshes off-peak (overnight)
• Use incremental refresh for large tables
• Monitor with the Capacity Metrics App

Diagnostic Tools

Related Documentation

Capacity Planning

CU smoothing, spike diagnosis, refresh scheduling

DAX Patterns

Optimized DAX patterns for AggieDB