Workaround for "Select All" Slicer with Filters?

[sflaming]

Hi there!

I'm hoping someone can help propose a solution to what seems like a simple problem. 

I'm trying to create an "event analysis" dashboard where users can view what other events were attended by the partcipants of a selected event. I've included sample screenshots below and can attach sample data if needed.

Here's the sample data: 

Here is the sample dashboard: 

I have removed the filter interaction between the slicer and the graph, so that you can view attendance data for the other events too. However, in this case, I would like to only see event attendance data for Contact #13, 14, 15 since they attended the Open House (selected in slicer). 

The solution I have come up with so far is to create a slicer for Contact ID which is filtered by the event slicer. Below is the outcome, which does have the desired result: 

The issue is with the "select all" functionality, which ignores the filters to the slicer, thereby including data for ALL records (Contact #13, 14, 15, 16), not only the ones who attended the selected event (Open House, in this example). Since the actual number of participants for each event is closer to 100+, obviously it isn't ideal to need to manually select each checkbox on the ContactID slicer in order to achieve the desired result. See below: 

I can't think of a way to do this without a slicer - maybe there is a measure I can write to only include the participants from the selected event? Any help or ideas would be much appreciated!

Thanks!

[v-kaiyue-msft · Accepted solution]

Hi @sflaming ,

1. Create a calculation table.

Table 2 =
SUMMARIZE('Table','Table'[Contact ID],'Table'[Event])

2. Create a measure.

EventCount = 
CALCULATE (
    COUNT ( 'Table'[Contact ID] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Contact ID]
            IN VALUES ( 'Table 2'[Contact ID] )
                && 'Table'[Event] = MAX ( 'Table'[Event] )
    )
)

3. Put the Event field in table2 into the slicer, and put the Event field and EventCount of the table into the bar chart.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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