Workflows

Aggregations for large Power BI models: when Import still wins

By Adrien Yvin ·

power-biaggregationscomposite-modelsperformanceimport

Direct Lake and a bigger capacity are not the only answers to a slow fact. If 90% of visuals hit month × product × region, build that grain as an aggregation table and let the engine hit Import at the speed of VertiPaq. Detail stays at transaction grain for drill.

Design

  1. Pick the hot grain from Performance Analyzer + DAX Studio (not from opinions)
  2. Build the agg table in Gold (SQL or Spark) — same keys as the dimensions the reports already use
  3. Import the agg table. Leave detail in DirectQuery, Direct Lake, or a large Import partition if you must
  4. Manage aggregations: map agg columns to detail table / columns, set precedence
  5. Verify with a query that hits the agg (Profiler / Server Timings / agg hit indicators)

If mapping is wrong, you pay for both tables and still scan detail.

Composite model hygiene

  • Relationships from agg and detail to the same dimensions
  • Do not duplicate measures “for the agg table.” Measures stay on the detail table; the engine rewrites
  • RLS: test both hit paths (agg and detail) with the same user

When aggregations lose

  • Every visual is already a different grain (true ad hoc). You need a better model or Direct Lake, not 15 agg tables
  • The agg is refreshed less often than executives think. Document SLA on the agg vs detail
  • You built the agg in DAX calculated tables on a 40M fact. Build it upstream

Alternative

User-defined aggregations vs automatic aggregations (Premium/Fabric): automatic can help; it is not an excuse to skip a designed Gold month-grain table you can certify.

Hitting a wall on a 500M-row fact that is mostly viewed monthly? Book a 30-minute call.