Different options to model many to many relationships in Power BI and Tabular

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.

[danielmudie]

"The kind of explanation where you just say yes because you want it to stop" you crack me up Alberto 😅 Also, super useful overview!

[cpcdev]

Your videos are really high quality. Crystal clear and I appreciate no ads.

[henrifanda]

Listening to your tutorial just brightened my day. Thanks for always sharing your knowledge through hands on practical. You are the best.

[bradj]

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 :)

[gabinnanda]

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.

[marcossaraiva]

Alberto, great insight once again regarding the model construction and relations. 👍

[wilsonman]

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? 🤔

[mohammadbakhshigohar]

Your Brilliant Videos have taken my sleep 😍😍

[Mirminman]

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!

[Bharath_PBI]

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 👍

[pedrosanteloy]

Great video! What if we merge Sports to Customers and work with Many to Many relationships from Customers and Sales?

[toniskolik]

Wao... awesome... This knowledge in terms of tabular models is also taught in your Tabular Course right?

[zchase]

something out of the box, is there a way to model the relationship depends on the filters.

[Rehan-osui]

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.

[seanhorowitz]

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).

[tommynguyen]

How about if I denormalize the sport and customer table, then create M2M to sales? In my experience, it performs quite okay.

[HenrikGronwall]

hahaha "the kind of explanation you just want to stop"🤣

[michaelsanchez]

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?

[arameshkumar]

* to * relationship have many drawbacks and some performance issues out there ?

[victorarayal]

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?