topic DAX Help - measure to select all matching rows from table given a slicer setting in DAX Commands and Tips

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

cwollett — Tue, 31 May 2022 19:10:01 GMT

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?

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

amitchandak — Wed, 01 Jun 2022 02:34:03 GMT

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

new measure=

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

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

cwollett — Wed, 01 Jun 2022 14:09:31 GMT

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.

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

Icey — Fri, 03 Jun 2022 07:33:07 GMT

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.