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.
Super UserThanks,
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- rohit1991
Hii jaryszek
The bridge table must still store only the unique ResourceID–KeyValueKey pairs for Power BI. The fact table can keep all 68M rows, but the mapping table should contain just the distinct combinations (one row per relationship). That’s enough for filtering and DISTINCTCOUNT to work correctly, and it reduces the bridge from millions of rows to a tiny, optimized table. Power BI never needs the repeated pairs only the unique links.