Count Slicer Selection in Related Table but Ignore Relationship

[tonysantangelo]

PBIX with mock data is here . I've got two tables with a relationship between them, many-to-many if it matters. How can I write a DAX measure for each row in Table1 with the number of selected in values in the slicer for Table2[Color]. For example, if blue, green, and orange are selected in the slicer, I want this measure to return 3 for every row in Table1. Here's what I've tried:

Count Slicer Selections = COUNTROWS(DISTINCT(Table2[Color]))

 This works fine in a card, but not when I display it in a table for Table1. It also works as intended if I delete the relationship between Table1 and Table2, but in the real use case I need this relationship for other purposes. What I'm currently getting instead is the number of colors available for each DeviceId after the slicer is applied, which varies by DeviceId.

[djurecicK2]

Hi @tonysantangelo ,

 Have you considered using a bridge table to avoid the many to many relationship?

Here is some additional information:

https://www.phdata.io/blog/how-to-create-a-bridge-table-in-power-bi/

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many

[tonysantangelo · Accepted solution]

I forgot some crucial information, so I'm going to create a new post. I can't find the delete button, so please ignore this post.