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 UserThanks,
how this table could look like and how relationships can be designed?
Best,
Jacek
Super UserHii 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.
- jaryszek
Thanks for the suggestion.
I already checked the bridge table and it does contain only unique ResourceID–TagID pairs. There are no duplicates. The reason the bridge is still very large is because we truly have tens of millions of real resource–tag relationships in the source data.So even with a slim, unique bridge, Power BI still has to scan ~68M links when I ask: “How many resources do we have per tag?” That number cannot be reduced by deduplication, because the data is already in its minimal form. How can I make this table thinner?
Best,
Jacek