Different options to model many to many relationships in Power BI and Tabular
HTML-код
- Опубликовано: 6 дек 2021
- There are two options to model many-to-many relationships using Tabular and Power BI: you can use either a regular bidirectional filter relationship, or a limited unidirectional relationship. In this video, we compare the performance of both options.
Article and download: www.sqlbi.com/articles/differ...
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... 
Наука
![MAF Teeski - Whole Thing (feat. VonOff1700) [Official Music Video]](http://i.ytimg.com/vi/SHWEj-ndz6g/mqdefault.jpg)
"The kind of explanation where you just say yes because you want it to stop" you crack me up Alberto 😅 Also, super useful overview!
Your videos are really high quality. Crystal clear and I appreciate no ads.
Listening to your tutorial just brightened my day. Thanks for always sharing your knowledge through hands on practical. You are the best.
Great explanation and demonstration. Thank you. Also, thank you for being honest about how you handled hearing this option the 1st time. It shows even the Pros are taught something sometimes :)
Alberto, great insight once again regarding the model construction and relations. 👍
Thanks alot i really appreciate your explanations and we are impatient to see the next video on how to use both models with conditional statements.
Excellent walkthrough, thanks!
Your Brilliant Videos have taken my sleep 😍😍
Fascinating. Thank you!
Really impressive !
That's challenging for data modelers, who target the model for self service bi users. So we wouldn't know what user queries are. We always followed the canonical way, rather than trying the "New" method as to avoid bi directional relationship. Now if bi directional relationship can be leveraged (with proper considerations) then there could be scenarios where model becomes complex especially when there are multiple star schemas and conformed dimensions. But still could keep it as a choice for modeling. Thank you 👍
Please not that in the original model the logical relationship between Sports and Customer is a single direction. The bidirectional filter in the bridge table is just an artifact to implement in a physical model a logical relationship. This is one of the few cases where the bidirectional filter is safe in the model, assuming you do not connect other relationships to the bridge table.
Thank you
The new many to many option is very interesting and I can definitely see some solid opportunities for applying the concept - thank you!. I am slightly unclear of the utility provided by this particular use case, given that filtering sports using the Customer Key on the Sales table provides you with no indication of how much of your sales for any given customer is related to the relative sport(s) associated with that customer (without doing that via a product key or what not).
Indeed, the many-to-many cardinality relationship can be used only when you don't have such information. If you have that additional data, you probably have another table and you create a many-to-many relationship with a bridge table and two one-to-many relationships.
hahaha "the kind of explanation you just want to stop"🤣
My comment was going to be similar to what was mentioned at the end. What if you add in an inactive relationship between customer and sales and use that one if you only want to filter by customer? 🤔
I have somes doubts. What happend when you have another dimension like a Calendar table in the second model (NEW) and the calendar table has a regular relation with Sales table?. Because we don't have a bidirectional filter (only we have a Many to Many relationship), what is the impact in the data when you filter in this way: Calendar table to get the sales for a year and then what is the value for Sports table or Customer table?
Nice video!
Hello, Is there way to find whether a column is based on another column in power bi desktop or service ?
Example- If database has two columns 'FirstName' and 'LastName'. They're concatenated to 'FullName'. Is there way to find in power bi that 'FullName' is made of two columns.
Wao... awesome... This knowledge in terms of tabular models is also taught in your Tabular Course right?
Data modeling is not the core of that training, but we have a module about relationships in that course. Another useful training is Data Modeling for Power BI, where we cover more data modeling scenarios: www.sqlbi.com/training/data-modeling/
How about if I denormalize the sport and customer table, then create M2M to sales? In my experience, it performs quite okay.
Great video! What if we merge Sports to Customers and work with Many to Many relationships from Customers and Sales?
Is it possible to create a model using two customer tables one with the direct relationship and one with the new relationship you have introduced and use whichever is advantageous?
Hi!
About the alternative model, shouldn't we relate the bridge table with sales based on the calculated column (Customer key/ Sport key)and not based on just customer key? If not, if we create a report trying to show sales amount by sport the number would be wrong. Am I right or i am not seeing something important?
hey, Victor. Think of Books sales where each Book has several Authors. If we want to see Sales by Books, we're ok. If we want to see sales by Authors, we hit M2M; same as Sports--> Sales here. If we could directly split Sales by Sport (i.e. having Sport key key in Sales tbl), that would not be M2M anymore.
something out of the box, is there a way to model the relationship depends on the filters.
* to * relationship have many drawbacks and some performance issues out there ?
I have always been too scared to try a many to many
How do we create a bridge table?
The bridge table contains data that must exist in the data source - you might have to transform it as you do with other data.
I haven't tested this but would not the new data model have problems with customers playing no sports?
Customers playing no sport would have no connection to the sales table.
Perhaps this could be fixed by adding a sport called "no sport" and one record for each customer, not already in the bridge table, to the bridge table connecting each such customer to this fake sport.
Connection is made by customer keys, so it's not a problem
@@natsarev I don't understand. If the customer plays no sport there would be no record for that customer in the bridge table and therefore no connection to the sales table in the new model.
To prevent this we could add records to the bridge table for all customer keys and with a null value for the sport.
That was my first thought too. When no sport is played, there is no way for a customer to filter sales. Their customer keys will not appear in the bridge table at all making a connection impossible