Forum Discussion
Direct Query to Multiple Datasets - Peformance
I applaud you for your decision to cut out unnecessary steps in your process.
The single biggest consideration you need to have is cardinality. Specifically the cardinality of the fields that you use to link the datasets together. That cardinality must be kept as low as possible. Low 100s preferred. 10K still somewhat possible. As soon as you hit 1M you are doomed. Truly doomed. If you want to get a really nice shock you can look at the generated DAX query in DAX Studio or others. I have seen DAX queries of 6GB (yes, Gigabytes) every.time.a.user.interacts.with.the.report - just because I hadn't checked the cardinality (which turned out to be 1.3M)
Thanks for your help. Would a bridge table be a solution potentially? So i could bridge the many side with the same masterdata (~4500 rows), turning it into a 1 to many, which ultimately then filters the million+ rows but within its own dataset?
I generally don't like bridge tables but in that scenario it might be helpful to shield the "other" data model from the excessive cardinality. Try it out.