Forum Discussion
Modelling big data query performance - how to improve?
Hii jaryszek
The correct modeling fix is to pre-aggregate or reduce the bridge (e.g., create a tag-to-resource mapping table with unique pairs, or materialize the many-to-many logic in your ETL). Power BI is not optimized to run DISTINCTCOUNT over a 68M-row bridge on the fly. Reducing the bridge cardinality is the only reliable way to improve performance.
Hii jaryszek
Power BI cannot shrink or optimize it further because it must load every real relationship. With tens of millions of pairs, the large table and heavy scans are unavoidable unless the source data itself is reduced.
Yes but this is real business data. How can i reduce it?
Best,
Jacek
Hi jaryszek,
Thank you for posting your query in Microsoft Fabric Community Forum. Also, thanks to rohit1991 , for those inputs on this thread.
your bridge table already has only unique Resource Tag pairs, Power BI can’t reduce it any further. Because the table itself is very large, Power BI has to scan all those rows whenever you do a DISTINCTCOUNT, and that’s why you’re hitting limits.In cases like this, the usual way to improve performance is to calculate the resource count per tag outside Power BI for example in your SQL/ETL step and then load that smaller aggregated table into the model. This avoids Power BI scanning the full 68M rows every time.
If you can share how your data is being prepared today, we can guide you on where this pre-aggregation can be added.
Hope this helps if you have any queries we are happy to assist you further.
Regards,
Community Support Team.thank you.
