Power BI aggregations: speed for very large models
Power BI aggregations speed up reports on very large data by keeping a small summary table in memory. Most visuals ask for totals by month or by region, so the summary can answer them at once. Only a request for fine detail goes to the big table in the source. The result is a fast report without importing every row. Here is how the feature works.
- Summaries in memory
- Detail stays in source
- Invisible to readers
Last updated
What are Power BI aggregations?
An aggregation is a pre-summed copy of a large table. For example, a sales table with a row per item sold becomes a table with a row per day and product. That second table is far smaller, so it fits in memory.
The author then tells the model how the two tables relate. When a visual asks a question, the engine checks whether the summary can answer it. If so, it uses the fast table. Otherwise it queries the detail table in the source.
Readers never choose between them. The summary table stays hidden, and measures keep pointing at the detail table. So the report behaves as before, only faster.
Building blocks of an aggregation design
Three pieces work together, and each has a storage mode to match its job.
The detail table
This is the large fact table. Microsoft documents that it must use DirectQuery, so its rows stay in the source.
The aggregation table
It holds the sums and counts at a higher grain. Authors usually import it, because in-memory answers are the whole point.
Shared dimensions
Tables such as Date or Product relate to both. Dual storage mode lets them serve either kind of query without a slow join.
When do aggregations pay off?
They pay off when DirectQuery is necessary and slow. If the whole table fits in an import model, import it instead, since that is simpler. So aggregations belong to the large end of the scale.
They also need a pattern in the questions. Summary tables help when most visuals group by the same few columns. However, if every reader drills to single rows, few queries will hit the summary.
There is a cost. The summary must stay in step with the source, or totals and detail will disagree. Therefore the refresh plan matters as much as the design.
How to set up Power BI aggregations
Microsoft's article walks through the dialog, and the outline below shows the order of work.
Create the summary table
First, build the grouped table in the source or in Power Query. Pick the grain from the visuals people use most.
Set the storage modes
Then set the summary table to Import and the shared dimensions to Dual. The detail table remains in DirectQuery.
Map the columns
After that, right-click the summary table and open Manage aggregations. For each column, choose the summarization and the matching detail column.
Prove the hits
Finally, test the main visuals and confirm that queries use the summary. The performance analyzer shows whether a visual sent a DirectQuery request.
What limits apply to user-defined aggregations?
Microsoft lists several rules. Whether a query can use the summary depends on the relationships or on the group-by columns you mapped. Also, chained aggregations across three or more tables are not allowed.
Security needs care. Row-level security must filter both the summary and the detail table in the same way. Otherwise a reader could see a total that includes rows they may not view.
There is also an automatic option. Automatic aggregations let the service build and manage summaries from query patterns. Microsoft ties that feature to Premium-level capacity and licenses, so check the documentation. Several summary tables can coexist, and a precedence setting decides which the engine tries first.
Questions about Power BI aggregations
Do Power BI aggregations change my measures?
No. Measures still refer to the detail table, and the engine redirects the query when the summary can answer it. So report authors write DAX as usual. However, only certain summarizations are supported, such as sum, count, minimum and maximum. A measure that needs row-level logic may therefore fall through to the source every time.
How do I know whether a query used the aggregation?
Use the performance analyzer in Desktop. Record the page, then look at each visual. When a visual shows a Direct query line, the request went to the source. If that line is missing, the in-memory table answered instead. For deeper proof, tracing tools can show the rewrite event, as Microsoft's article describes.
Are automatic aggregations in Power BI the same thing?
They share the idea but differ in effort. With user-defined aggregations, you design and map the summary table yourself. With automatic aggregations, the service studies query history and maintains the summaries. Microsoft documents capacity and license requirements for the automatic kind, so read the current article before you plan for it.
Can Power BI aggregations work with row-level security?
Yes, but the rules must cover both tables. Microsoft advises that security filters apply to the aggregation table and the detail table alike. A filter on a shared dimension usually does this cleanly. So design the security on dimensions, and then test with accounts from different roles before launch.
Can you tune a slow DirectQuery model for us?
Yes. Power BI Agency designs aggregation tables, storage modes and refresh plans for large models. We are a division of Sataware Technologies and independent of Microsoft. The service runs online inside your own tenant, with a weekly demo of progress. A free 30-minute assessment comes first, and we reply within 2 hours.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.