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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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