User-Defined Aggregations in Power BI

Aggregations dramatically improve DirectQuery performance by serving common queries from pre-computed summary tables in memory.

How Aggregations Work

Power BI automatically routes queries: - High-grain queries hit the aggregation table - Detail queries go to DirectQuery source - Users see seamless performance

When to Use Aggregations

• Large fact tables (millions+ rows)
• DirectQuery to slow sources
• Common summary-level queries
• Dashboard performance issues

Setting Up Aggregations

Step 1: Create Aggregation Table Build a summary table with: - Grouped dimensions (Date, Region, Product Category) - Aggregated measures (Sum of Sales, Count of Orders) - Lower granularity than source

Step 2: Import Aggregation Table Bring the aggregation table into the model using Import mode for in-memory performance.

Step 3: Configure Mappings In model view, configure aggregation settings: - Map summary columns to detail columns - Specify aggregation functions - Set precedence if multiple aggregations

Step 4: Hide and Test Hide aggregation tables from report view. Test to verify queries use aggregations.

Best Practices

Right Granularity Aggregate to commonly queried levels: - Daily instead of hourly - Category instead of product - Region instead of store

Multiple Levels Create cascading aggregations: - Monthly aggregation - Daily aggregation - Detail DirectQuery

Monitor Usage Use Performance Analyzer to verify aggregation hits.

Fabric Enhancement

In Fabric, Direct Lake mode provides aggregation-like performance without manual setup.