Filtering on many to many relationship

Question

My data setup is like this:&nbsp;I have one table with ReviewId as the primary key, and another table that looks like this:&nbsp;ReviewId&nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; Platform1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xbox1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; windows2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xbox2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; mobile3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; xbox...&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;...&nbsp;And so on. What I want to do is create a slicer on the platform feature, so if I select "xbox", it filters the reviews so I only have the reviews that have an "xbox" entry in the previous table.&nbsp;The ReviewId's and Platforms are linked by another ID, "BigId". So one BigId generates multiple reviews and multiple platforms, which I believe is the reason that the normal slicers do not function as I want. Thanks for any help.

anonymous · Answer

Can you screenshot your data model?

biofio · Answer

Here is what I am working with:relationshipsreview platform relationshipmain reviews table

anonymous · Answer

HI&nbsp;biofio,
&nbsp;
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.
&nbsp;
Regards,Xiaoxin Sheng

biofio · Answer

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.&nbsp;* My bridge table (Bridge) looks like (ignore BigId):&nbsp;Linked to this, I have a one-column table:&nbsp;PlatformsTargetedPlatformxboxmobilewindows...&nbsp;ReviewsReviewId | Latency | ...38&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 3.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| ...&nbsp;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.

anonymous · Answer

HI&nbsp;biofio,
&nbsp;
I think you have misunderstood my suggestion.First, my formula is used to extract all platform informations to create a unique table with platforms type.
&nbsp;
Platform Bridge =
DISTINCT (
    UNION (
        VALUES ( Table1[TargetedPlatforms] ),
        VALUES ( Table2[TargetedPlatforms] ),
        VALUES ( Table3[TargetedPlatforms] )
    )
)

&nbsp;
After create platform information table, you can create relationships between above table and platform columns from other tables.(multiple many to one relationship)
For example:Table1 TargetedPlatforms to 'Platform Bridge' Platform Bridge,Table2 'TargetedPlatforms' to 'Platform Bridge' Platform BridgeTable3 'TargetedPlatforms' to 'Platform Bridge' Platform Bridge...
&nbsp;
Finally, you can use 'Targeted Platforms' column to create slicer (from bridge table) to filter with all tables.
&nbsp;
&nbsp;
Regards,
Xiaoxin Sheng

Forum Discussion

Filtering on many to many relationship

5 Replies

Related content

Relationship

Relationships

Relationship Issue - Keep Filter ISsues.

Relationship Issue

Creating relationships