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
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.
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
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,
JacekHi 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.
