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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.