How to model many-to-many relationships in Power BI

How to model many-to-many relationships in Power BI: 

 ✔regular relationships with bidirectional filter

✔ many to many relationship with no bidirectional filter

Alberto Ferrari has performed a detailed analysis of queries executions on the two models in order to compare their performance:

✔First model tested: the canonical model: contains a bridge table and a bidirectional filter so that filter passes to the fact table.

👍 For queries involving only dimension table: degree of parallelism is good, calculation performed by the #Storage_Engine

👎 When queries become more complex involving the bridge table, the #Formula_Engine is required, which slows down the query time execution.

✔Second model tested: the bridge table is related to the fact table with many to many relationship (no bidirectional filter in the model)

👍Model is more constant in its performance, time is always the same for all queries, all queries tested are being executed in the #Storage_Engine

👎 Queries are always traveling the bridge table , there is a more constant loss in performance, regardless of the dimensions used for filtering

#modeling #model #sqlbi #filter #relationships #DAX #performance #queries #storage_engine #formula_engine