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/...ridgeTable.jpg

I have started to create an Expanded Table Diagram. Here is the picture of my Expanded Table: https://people.highline.edu/mgirvin/...ndedTables.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?

Sincerely, Mike Girvin

[mgirvin]

Thanks for the reply back, Nick M and ChandeepChhabra

I have to say, dealing with this forum is like have my teeth drilled without novocaine. This is the fourth time in a row, that I post questions and do not get notified and have a difficult time even finding my original posts. So I am sorry about my late reply.

ChandeepChhabra, your solution is clever in how you used GENERATE. Why did you do that rather than build a bridge table? Your solution does not answer my question, however.

My original question was, 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 : )
I do not see a way to attach my file... : (

Sincerely, Mike Girvin

[Anonymous]

@mgirvin 

You can attach the file using a link to a dropbox/ one drive. 

Generally, bridge tables are used as filters. Something like 

=CALCULATE( 
     COUNTROWS(fSales),
     BridgeTable)

but if you can upload your sample I will look at more. The concept of expanded tables isnt the easiest one for sure.

-Nick

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

Sincerely, Mike Girvin

[ChandeepChhabra]

Hello @mgirvin 

First off I am a big fan of your work! It's a pleasure interacting with you here

Coming to your question. You are right when you say that

1. Filters won't propagate from one facts table (fsales) to another facts table (BridgeAuthorBooks) connected via a lookup table (dbooks) 
2. Again a column from dAuthors won't be able to filter fsales table

I had the same problem a while a ago and solved it using creating a psuedo filter context using =GENERATE function. You may take a look at the Power BI Model here

Thanks

[Anonymous]

@mgirvin 

Any chance you upload some sample data? The concept of expanded tables is not an easy one and would like to look at the actual data if possible.