Forum Discussion

biofio's avatar
biofio
Frequent Visitor
8 years ago

Filtering on many to many relationship

My data setup is like this:   I have one table with ReviewId as the primary key, and another table that looks like this:   ReviewId     |      Platform 1                        xbox 1          ...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Can you screenshot your data model?

  • biofio's avatar
    biofio
    Frequent Visitor
    8 years ago

    Here is what I am working with:

    relationshipsreview platform relationshipmain reviews table

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      HI biofio,

       

      I'd like to suggest you create a calculated table with merged union platform types, then use this table to link detail tables which you used.

      Platform Bridge =
DISTINCT (
    UNION (
        VALUES ( platformInfo[TargetedPlatforms] ),
        VALUES ( WorkOrder[TargetedPlatforms] )
    )
)


      After these steps, you can use above bridge table to filter with all detail tables.

       

      Regards,
      Xiaoxin Sheng

      • biofio's avatar
        biofio
        Frequent Visitor
        8 years ago

        I think I am still confused. So if I have one many-to-many relationships (let's say Platforms:Reviews), and I construct a bridge table*, then how do I actually use that bridge table to filter? I want to be able to select one a subset of platforms and give me only those reviews that have a relationship with those platforms.

         

        * My bridge table (Bridge) looks like (ignore BigId):

        reviews.PNG

         

        Linked to this, I have a one-column table:

         

        Platforms

        TargetedPlatform

        xbox

        mobile

        windows

        ...

         

        Reviews

        ReviewId | Latency | ...

        38           | 3.5         | ...

         

        So, Reviews has a 1:M with Bridge, and Platforms also has a 1:M with Bridge. Then how do I create my slicer based on this? I had figured do it on the "TargetedPlatform" field of Platforms, but that did not work.