When working with large datasets in Power BI, my main focus is always on optimizing performance right from the data source to the report layer. I usually start by pushing as much processing as possible to the data source level. For instance, instead of importing a massive table and filtering it later in Power BI, I use SQL views or parameters to pre-aggregate or filter the data before it even reaches the model. This significantly reduces memory usage and refresh time.
In Power BI, I also rely heavily on proper data modeling. I keep the model in a star schema structure — fact tables containing numeric and transactional data, surrounded by smaller dimension tables for descriptive fields. This not only makes the model more efficient but also improves DAX performance. For example, in one of my projects dealing with over 200 million rows of transactional data from a retail system, I separated the fact table by year using incremental refresh. That allowed Power BI to process only the latest data during refresh instead of reloading everything.
Another important practice I follow is controlling column cardinality. I avoid including unnecessary columns, especially text columns with high uniqueness like IDs or long descriptions. If those are required for drill-through, I keep them in a separate lookup table. Compression efficiency improves drastically when cardinality is low.
In terms of DAX, I always write measures efficiently by avoiding complex row-by-row calculations or nested iterators. Instead, I use variables and pre-aggregations whenever possible. For example, I replaced a complex CALCULATE + FILTER combination with a SUMX over a summarized table, which cut my calculation time in half.
One challenge I faced was when using DirectQuery mode with a very large dataset in SQL Server — even though it avoided data import, query response time was slow due to network latency and complex joins. To solve this, I applied aggregation tables in Power BI. Essentially, I created pre-summarized data at a higher level of granularity that Power BI could query first, and only drill down to the detailed table when required.
A limitation with large datasets is memory constraint in Import mode and slower response in DirectQuery mode. The alternative depends on the use case — for real-time dashboards, I prefer DirectQuery or a hybrid model (Composite Model), whereas for analytical reports, Import mode with incremental refresh gives the best balance of performance and flexibility.
Overall, handling large datasets efficiently in Power BI is about combining good data modeling, efficient DAX, optimized storage mode, and close collaboration with the data source design.