Forum Discussion
Super UserModelling 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.
Super UserHii 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.
- jaryszek
Thanks,
how this table could look like and how relationships can be designed?
Best,
Jacek- rohit1991
Hii jaryszek
create a slim mapping table that holds only the unique ResourceID–TagID pairs. This becomes your new bridge and connects with simple 1: relationships from Resources and from Tags. By reducing the table to distinct combinations only, Power BI no longer has to scan 68M rows for every DISTINCTCOUNT, and the performance issue disappears.
- jaryszek
thank you.
the issue is that this is the smallest grain:
Best,
Jacek
