DAX Help - measure to select all matching rows from table given a slicer setting

[cwollett]

I need some assistance with a DAX measure. I feel like what I want to do is possible, but I'm not sure how to do it.

For simplicity's sake, let's say I have a table that looks like this:

Presentation	Name	Room	Start	End	Date
Science 1	Ann	E123	8:00	8:50	6/1/2022
Science 2	Betty	E125	9:00	9:50	6/1/2022
Science 3	Carol	E127	10:00	10:50	6/1/2022
Tech 1	Denise	W123	8:00	8:50	6/1/2022
Tech 2	Esther	W125	9:00	9:50	6/1/2022
Tech 3	Fran	W127	10:00	10:50	6/1/2022
Engineering 1	Fran	W123	10:00	10:50	6/1/2022
Engineering 2	Esther	W125	8:00	8:50	6/1/2022
Engineering 3	Denise	W127	9:00	9:50	6/1/2022
Math 1	Carol	E123	10:00	10:50	6/1/2022
Math 2	Betty	E125	8:00	8:50	6/1/2022
Math 3	Ann	E127	9:00	9:50	6/1/2022

I want to have a slicer with the room options, and when I select a value in that slicer, it triggers a measure calculation that returns TRUE/1 for any person using that room in the table. For example, if I select room E123, since both Ann and Carol have a presentation in that room, it would mark all rows for Ann and Carol as TRUE.

I've tried doing this by manipulating slicer filtering:

• Filter1 with room list
• Filter2 with person list
• Have Filter1 filter Filter2
• Have Filter2 filter Table

Unfortunately, "Select All" on a slicer also selects any undisplayed items as well. Counter-intuitive in my opinion, but the fact of the matter is it makes using just slicers not work.

Anyone have thoughts? Nudges as to the right DAX formulas/expressions that might work?

[Icey · Accepted solution]

Hi @cwollett ,

Try this:

Room Slicer = VALUES('Table'[Room])

Measure = 
VAR SelectedRoom_ =
    VALUES ( 'Room Slicer'[Room] )
VAR RelatedName_ =
    SUMMARIZE (
        FILTER ( ALL ( 'Table'[Name], 'Table'[Room] ), [Room] IN SelectedRoom_ ),
        [Name]
    )
RETURN
    IF ( MAX ( 'Table'[Name] ) IN RelatedName_, 1 )

For more details, please check the attached .pbix file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@cwollett , if the room filter is coming from table or joined table 

new measure=

if( countrows(Table)+0 >0, 1, 0)

[cwollett]

Unfortunately that does not work. The filter cannot be tied to the table or it filters out more rows than I'd like. In the image above, I don't have that filter acting on that table.