There comes a point where Power BI meets its natural limit: the data is too large to fit comfortably in memory, but you still need fast, interactive reports. This is where many give up and blame the tool. In fact, there are two advanced features — composite models and aggregations — designed precisely for this problem, and mastering them separates those who make reports from those who architect solutions.
The Import vs DirectQuery dilemma
Power BI offers two modes to connect to data. Import brings everything in, compressed and lightning-fast — but limited by memory and stale between refreshes. DirectQuery leaves the data at the source and queries it in real time — always current and with no size limit, but slower and weighing on the database. For years, you had to choose one. Not anymore.
Composite models: the best of both worlds
A composite model lets you mix Import and DirectQuery in the same report. You can have the large fact tables in DirectQuery (always current, without filling memory) and the smaller dimensions in Import (fast). There is no longer a binary choice: each table lives in the mode that makes sense for it.
Aggregations: speed without sacrificing detail
Aggregations take the idea further. You keep in Import a summarized version of the data — for example, sales by day and region — and leave the full detail in DirectQuery. When someone opens a report at the summary level, Power BI answers from the small, fast table; only when someone drills into detail does it go to the large source. The user does not even notice — they always see fast answers.
A concrete case
Imagine a retail chain with a billion transaction rows — impossible to import in full. With an aggregation of sales by day/store/category (perhaps a few million rows), 95% of managers' questions — totals, trends, comparisons — are answered instantly from the summarized table in memory. The 5% that need the detail of a specific receipt fall to DirectQuery at the source. The result: interactive reports over volumes that "did not fit" in Power BI, without duplicating everything nor waiting minutes for each click.
The price of power: complexity
These features are powerful but not free in effort. They require designing the aggregations well, ensuring the relationships between layers are correct, and testing that Power BI really uses the summarized table when it should. It is data engineering inside Power BI — and that is why it is the step that separates the advanced user from the architect.
When it is worth it
It is not for every report. A model of a few million rows lives very well in plain Import. Composite models and aggregations come in when the data is too large for Import but you need Import speed — the territory of large volumes. Using them without that need is adding complexity with no return.
In practice
If you are hitting Power BI's memory limit or suffering with slow DirectQuery, it is not time to give up — it is time to learn composite models and aggregations. They are the answer designed for exactly that problem. Are your reports limited by the size of the data, or have you already explored the layers that allow scaling without losing speed?
