PL-300: how to implement aggregations in Power BI models

I will show how to implement aggregations (aggregation tables) in Power BI models — a valuable skill for the PL-300 and to improve performance in scenarios with large data volumes. Aggregations allow common queries to be answered with summarized in-memory tables, reducing the load on the model or the DirectQuery source.

What you need to know

An aggregation table is an additional table in the model that contains summarized data (for example, sales by month, product and region). When the query engine determines that a visual can be answered by the aggregated table, it uses it instead of the detailed table, speeding up response time. Aggregations are especially useful when you use hybrid models (import + DirectQuery) or DirectQuery over large data warehouses.

Conceptual example: you have a FactSales table with millions of rows. By creating an AggregatedSales table with columns Year, Month, ProductCategory and TotalSales (sum of SalesAmount) and configuring relationships and mappings, queries that request sales by Month/ProductCategory use AggregatedSales in memory, avoiding scanning FactSales.

How it works

Key steps to implement aggregations in Power BI Desktop:

1. Identify the most frequent queries/visuals and the required levels of granularity (e.g., year/month, product category).
2. Create the aggregation table: it can be generated at the source (SQL), in Power Query (Group By) or as a DAX Calculated Table.
3. Add key columns in the aggregated table that correspond to dimension columns (dates, product, region) and keep types/codes compatible.
4. Configure the Manage Aggregations property in Power BI Desktop, mapping the measures/columns from the detailed table to the aggregated table.
5. Test and validate using the Performance Analyzer and query traffic (check whether queries use the aggregated table or still go to DirectQuery).

Practical example: create an aggregated table in Power Query and use a simple measure.

// Em Power Query (M) - criar agregação por Year, Month, ProductCategory
let
    Fonte = Sql.Database("servidor", "base"),
    Sales = Fonte{[Schema="dbo",Item="FactSales"]}[Data],
    AdicionarColunaData = Table.AddColumn(Sales, "Year", each Date.Year([OrderDate]), Int64.Type),
    AdicionarMes = Table.AddColumn(AdicionarColunaData, "Month", each Date.Month([OrderDate]), Int64.Type),
    Agrupar = Table.Group(AdicionarMes, {"Year","Month","ProductCategory"}, {{"TotalSales", each List.Sum([SalesAmount]), type number}})
in
    Agrupar

After loading AggregatedSales and FactSales, create the normal relationships with dimension tables (e.g., DimProduct, DimDate). In Power BI Desktop go to Model view > Manage aggregations (or select the detailed table > Manage Aggregations) and do the mapping:

1. Map the measure FactSales[TotalSales] to AggregatedSales[TotalSales] with the Sum function.
2. Map granularity columns (FactSales[Year] → AggregatedSales[Year], etc.).

The engine will use the aggregated table when the query can be answered at the defined level of granularity. If the visual requests detail (row by row) the engine will fall back to FactSales.

Common mistakes

• Attempting to aggregate without ensuring matching types and codes between dimension columns: format differences prevent the aggregation from being used. Always align types and keys (integer/text/dimension keys).
• Mapping measures incorrectly (using Count instead of Sum) or forgetting derived measures: measures that depend on complex context require additional definitions for the aggregation to work correctly.
• Not testing filter scenarios: if a visual uses filters that are not present in the aggregated table, the engine may ignore it and go to DirectQuery, causing unexpected slowness.

How to practice

Practice by creating a simple model with a large FactSales (you can simulate by generating many rows in SQL or Power Query) and several dimension tables. Create different aggregated tables (by day, month, quarter) and configure Manage Aggregations. Use the Performance Analyzer to verify that queries use the aggregations. For exam preparation, complete the OFFICIAL Practice Assessment and consult the Microsoft Study Guide (both free) to see how this skill appears in the skills measured.

In summary

• Aggregations reduce the need to scan detailed tables and speed up responses to common queries.
• Creating aggregations requires tables with appropriate granularity, columns aligned to dimensions and correct measure mapping.
• Test with the Performance Analyzer and validate filters and types to ensure the engine uses the aggregated tables.
• Practice with real examples and use Microsoft official resources (Practice Assessment and study guide for free).