Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tonysantangelo
Frequent Visitor

Count Slicer Selection in Related Table but Ignore Relationship

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.

1 ACCEPTED SOLUTION
tonysantangelo
Frequent Visitor

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. 

View solution in original post

2 REPLIES 2
djurecicK2
Super User
Super User

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
Frequent Visitor

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.