Results 1 to 5 of 5

Thread: Expanded Table for Many To Many Relationship?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Expanded Table for Many To Many Relationship?

    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

  2. #2
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Expanded Table for Many To Many Relationship?

    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

  3. #3
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Expanded Table for Many To Many Relationship?

    Anyone? Any ideas?
    Sincerely, Mike Girvin

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expanded Table for Many To Many Relationship?

    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

  5. #5
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Expanded Table for Many To Many Relationship?

    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
    Sincerely, Mike Girvin

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •