Expanded Table for Many To Many Relationship?

[mgirvin]

Dear Team,

I have a Data Model that contains a Many To Many Relationship and a Bridge Table. Here is the picture of my Data Model: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/01-DataModelWithBridgeTable.jpg

I have started to create an Expanded Table Diagram. Here is the picture of my Expanded Table: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/02-ExpandedTables.jpg

Is the Expanded Table Diagram correct?

More specifically, since the Expanded Columns in a Native Table are the result of a One-To-Many Relationship, where the One Side Columns move to the Many Side Table, in my diagram, the dBook columns flow from the One Side to the fSales Many Side, but do the columns from the Bridge Table also flow, as Expanded Columns, to the fSales table? I can't make sense of this because that would mean that if the columns from the Bridge Table flowed to the fSales, they would have to move from the Many Side to the One Side!?!?

Can someone help me create the correct Expanded Table Diagram for a Many-To-Many Relationship and a Bridge Table?

 

[mgirvin]

When I do a test to see if the columns from the dAuthor and BridgeAuthorBooks (Bridge Table) are present in the fSales Expanded Table, I created the measure:

=COUNTROWS(fSales)

then when I:

1) Use dBooks BookID Column in a PivotTable, and I get the correct count for each BookID.
2) Use dAuthors AuthorID Column in a PivotTable, and I get the wrong count, I get the count of all records in the fSales Table.
3) Use BridgeAuthorBooks AuthorID or BookID Columns in a PivotTable, and I get the wrong count, I get the count of all records in the fSales Table.

This test confirms that the dAuthor and BridgeAuthorBooks (Bridge Table) are NOT present in the fSales Expanded Table, right?

 

[mgirvin]

Anyone? Any ideas?

 

[Matty]

Hi Mike,

I suspect the only way to make this model work would be to create a single table in Power Query that brings BridgeAuthorBooks and dAuthors together so that you have one table with the columns BookID, AuthorID and Author in it.

You'd then join this new table to dBooks via BookID.

Let me know how you get on...

Cheers,

Matty

 

[mgirvin]

Matty,

That is exactly what I did. The Bridge Table in the picture is the result of using Power Query to build Bridge Table and then build the relationships. My question is, do the columns from the Bridge Table appear in the Fact Table Expanded Table Diagram? As I mentioned in above posts, I theorize that they do not because any filters from the Bridge Table cannot traverse the Many-To-One Relationship from the dBooks Table and then on to the Fact Table, and this is illustrated in my Expanded Table Diagram. I further tested with a Measure (Post #2 ), and this seems to verify that the Bridge Table Columns are not present in the Fact Table. I am posting here to try and get another opinion or two that confirm what I am theorizing : )

Thanks for your post back, Matty!

Sincerely, Mike Girvin