What it does

An aggregation table is a pre-summarised, in-memory copy of a large DirectQuery fact table. Power BI redirects queries to it when the query's grain is covered, and passes everything else straight through to the source. Report authors and consumers never reference the aggregation table — they query the detail table as normal.

Key facts

• User-defined aggregations are configured through the Manage aggregations dialog. Each aggregation column maps to a summarisation function, a detail table and a detail column.
• The detail table must be in DirectQuery storage mode. Aggregations don't apply to Import detail tables.
• The aggregation table itself is normally set to Import. Once you set an aggregation table's storage mode to Import, you can't change it back.
• Aggregation tables are hidden from Report view, and Manage aggregations hides them for you on Apply all. Users with read-only access can't query them, which is what keeps row-level security honest.
• Chained aggregations across three or more tables aren't allowed, and neither are duplicate aggregations pointing at the same summarisation, detail table and detail column.
• Row-level security expressions must filter both the aggregation table and the detail table. An expression that filters only the aggregation table is blocked. One that filters only the detail table is allowed but means that role never gets an aggregation hit.
• Grouping by a foreign key on an inactive relationship and relying on USERELATIONSHIP isn't supported. TREATAS works instead, provided there's no active relationship between the tables.
• Related dimension tables should be set to Dual so they can serve both the Import aggregation and the DirectQuery detail efficiently.
• Automatic aggregations are the managed alternative: a machine-learning model trains on a seven-day query log and maintains the aggregation cache itself. Supported for Power BI Premium per capacity, Premium Per User and Power BI Embedded models over DirectQuery, against Azure SQL Database, Azure Synapse dedicated SQL pool, SQL Server 2019 or later, Google BigQuery, Snowflake, Databricks and Amazon Redshift.
• With automatic aggregations, only the first scheduled refresh of your chosen Day or Week frequency runs a training operation; the rest are refresh-only. Training has a 60-minute limit and resumes on the next run if it doesn't finish. You must be the model owner to configure it.
• Direct Lake tables don't support user-defined aggregations.

When to use / skip

Aggregations are worth building when you've committed to DirectQuery for volume or freshness reasons and the reports are dominated by a small number of predictable, high-level questions — sales by month by region, that sort of thing. The pattern pays for itself quickly there. They're not worth it when the reporting is genuinely exploratory at detail grain, because you'll never get a decent hit rate and you'll have added a maintenance burden for nothing. And if the model could just be Import, make it Import — an aggregation table is a consolation prize for not being able to.

Configuration decisions

• The grain of the aggregation table, which is the whole design. Too fine and it doesn't compress; too coarse and it never gets hit.
• Whether the aggregation is built by ETL in the source, by an M expression, or as a DirectQuery table over a columnstore-indexed summary — all three are valid and they load capacity differently.
• Which dimension tables move to Dual, and whether every dimension used in an aggregation relationship is covered.
• Whether row-level security expressions can be written to filter both sides, because if they can't, that role gets no benefit.
• User-defined or automatic: hand-built control versus a self-tuning cache you don't have to maintain.

Gotchas

• Hit rate is the metric that matters and nobody measures it. A slicer on a column not in the aggregation table sends the whole query to the source, and the report just feels slow again.
• Setting the aggregation table's storage mode to Import is one-way. Get the design right before you flip it.
• An RLS expression on a dimension that only reaches the detail table silently costs those users every aggregation hit. It's not an error, just quietly slow for one group of people.
• Automatic aggregation training and refresh are heavy on both the capacity and the source. Pushing the query coverage slider up increases the number of aggregations that have to be built and refreshed, and can cause timeouts.
• Automatic aggregations train on past queries. New questions miss the cache until the next training cycle picks them up, so a newly published report can feel slow for a day.

Consultant notes

• Sell aggregations as a performance safety net for a DirectQuery decision that's already been made, not as a feature in its own right. Clients who hear "aggregations" first tend to over-engineer them.
• Automatic aggregations are the right starting point if the licence allows it. Prove the benefit exists before anyone spends a fortnight hand-building tables.
• Instrument the outcome. Performance Analyzer plus the query log will tell you whether hits are happening; opinions won't.
• Warn the client that the aggregation grain has to be revisited when reporting requirements change. It's not fire-and-forget, and it's the first thing to check when "Power BI got slower".

---

Worth another look if aggregation support arrives for Direct Lake, or if the supported source list for automatic aggregations grows.