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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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