Many-to-Many Relationships - The Whiteboard #12

The many-to-many cardinality relationship in DAX is not what a many-to-many relationship is for the rest of the world...


#thewhiteboard

[GeraldHilbers]

Marco, this is a great serie of Whiteboard-sessions !!! I would appreciate if you could add a session about the do's and dont's of bidirectional relationships.

[cesarsaldana]

Solution 2 is very cheap because, who is going to create a brand-only budget... real life examples are what we need, Mr. Russo.

[erinfield]

3:00- bridge table type 1 (new information) (one-many--many-one)
7:30- bridge table type 2 (no new information) (many-one-one-many) (won't slow down your model if there are only a few hundred rows)

[erinfield]

7:45- why does Product to Brand need to be bi-directional? We have this second kind of bridge table in real estate: a borrower can have multiple collateral, and conversely, a collateral can be connected to multiple loan numbers (ie. pari-passu loans). So you need an intermediary loan-to-property code table between the Loan and Property tables. This is one-to-many-to-many-to-one, and it's uni-directional and it works just fine for Loan to filter Property.

[osamaabd-elmohsen]

Thank you for your great explanation, if I have a table with some of codes and its category and another table of the codes and its balance and stock in different stores and also category column (not all the codes are existed in the second table) now I want to create a relationship between category column in the two table (but power bi tell me it should be unique category value in both table and that is not my case ), I have tried middle table many to one one to many but it does not work ?

[MilhouseBS]

10:55 for when to use built in * to * instead of own table. It depends on size of intermediate table.

[leandrovianadasilva]

Hi Marco,

You guys always deliverying great stuff, thanks a lot!!!

But I've a doubt.. In this model, how can I distinct count the Costumer on my fact table, is it possibel doing that? Since I dont have a Costumer FK at my fact tabel..

In advanced, thanks a lot for the help! =)

[prashantadhiakri]

Hi, Your all videos are very impressive and I am new to this Power BI, So please explain in coming videos how to create or Cohort analysis or retention analysis.

[kalyanreddy]

Sir, I have question
I have
customer dim connect to salesfact
Supplier table connect to salesfact
If i want to analyse sales by customer and supplier name. In this case it called as one to one relation as the above example. Both foreign keys are in fact tabke and each customer and supplier is have no relation from dim tables till confused.
Can you clarify this

[tonib]

Min 9.37...please correct me if i'm wrong but from my understanding all tables (Product, Brand and Budget) have Single (not Both / Bi-Directional) relationships...is that true

[robertohio]

What are the pro’s and con’s of creating the Brand table in DAX vs Power Query?

[sandeepbarge]

Thanks for the video. Can you please explain how does suggested data modelling technique help in ensuring account balance is not considered twice while calculating totals in the very first example you gave?

[mattheosprotopapas]

Hi Marco,

I was wondering if a change in the granularity of the fact table, to eliminate the many-to-many, would benefit performance or not?

To try to explain, in the first example, if we stored an account transaction multiple times in the fact table, one for each customer of the account, that would transform the model to a simple star model. With no need to have a bridge CustomerAccount table, we would just create direct relationships from the fact (which now stores data in a Customer X account granularity) to the two dimensions.
On the other hand, we'd also need to change the DAX of most measures as they would now become more complicated (and non-additive). If for example we wanted to count the number of accounts with transactions within a given period, we'd need a DISTINCTCOUNT instead of a COUNT.

Would you expect such an approach to benefit or worsen performance? I'm mostly interested in Direct Query settings nowadays - which is perhaps more complicated- as my current customer likes direct query a lot.

Buon anno!

[nikolagrkovic]

at 6:30, that is how my models look like 🤣