Return Other Records for Slicer Value Too

[hannats]

I have two tables – sessions and participants – that I am joining in Power BI. Every session can have one or more participants, so for example:

Session Table

Session

S1

S2

S3

Participants Table

Session	Participants
S1	Bob
S1	Jim
S1	Tom
S2	Bob
S2	Jane
S3	Larry

I want to be able to have a slicer to select a participant and have it return all of the sessions that the selected participant is part of including the other participants. So for example, if I select “Bob” in my filter, I want it to return the rows below. In other words, I need to see all of the sessions that Bob attended PLUS see all of the other people who attended those sessions as well:

Session	Participants
S1	Bob
S1	Jim
S1	Tom
S2	Bob
S2	Jane

Any thoughts?

[ChandeepChhabra]

@hannats

Try this measure

=CALCULATE(CONCATENATEX(Participants,Participants[Participants],", "),ALL(Participants[Participants]))

The only difference being the participants will appear in a single cell against each session

Hope it helps, Thanks

[hannats]

@ChandeepChhabra   Ok, I think this could lead somewhere. But still not sure how I use it in a filter? Can I maybe do a FIND DAX statement or something with the string? See SessionsParticipants.pbix here.

@Ashish_Mathur This is similar to what you are proposing I think.

[Ashish_Mathur]

Hi @hannats,

Did you try my solution?

[hannats]

@Ashish_Mathur  I see what you are doing and it makes sense, but what if a user wants to select something other than Alice? It's not intuitive for them to do so. Especially with a Power BI slicer.

[Ashish_Mathur]

Hi,

Why would it be difficult?  Why can the user not select any other names in the filter/slicer?

[hannats]

Maybe I am misunderstanding, but it looks like you can't select "Alice" but rather you have to select all entries where Alice exists. That would be difficult every time you want to pick a new name. 

[Ashish_Mathur]

Hi,

You need not check the individual boxes there.  You may just type Alice in the search box.

[hannats]

In Excel that is possible. In Power BI slicer you still have to select the individual boxes, correct?

[Ashish_Mathur]

Yes, i think so.

[ChandeepChhabra]

@hannats To make this work use all the fields from the participants table. Sending you the revised pbix

[hannats]

Ok, but now when I select "Bob", it returns all sessions, including the session that Bob is not a part of (i.e. S3 - Third Session). I want to be able to select "Bob" and have it only return the sessions that Bob is in. Also would still need to list out the pariticpants as rows. Could the concatenated string maybe be used in some kind of lookup?

[ChandeepChhabra]

@hannats

I am not sure why is that happenining. It working fine for me

Also I modified the measure a bit to make each participant appear in a new row

List = CALCULATE(CONCATENATEX(Participants,Participants[Participants],UNICHAR(10)),ALL(Participants[Participants]))

[Ashish_Mathur]

Hi,

I have answered a similar question here - Filter a column of a Pivot Table on a certain condition but also show other items from that column.

Hope this helps.

[Anonymous · Accepted solution]

My solution would be to have a third table which is a unique list of Sessions.  In your table relationships, join your current two tables just to this Distinct Sessions table.

Now you should be able to create a visual that contains Participants in your Participants table, which when clicked should filter your Sessions table and display just those records.

[hannats]

@Anonymous  Ok, I think you actually nailed it. Here is what I did - it seems to work!

• Created a new Slicer table for just the distinct session / participant combos (really just a copy of the participants table)
• Used the Participants field from that Slicer table in the slicer
• Joined the Slicer table to the main Sessions table and made sure to filter in both directions

[hannats]

@Anonymous  Hmm....  Not seeing how that works. See "distinct" PBIX file here - maybe I am misunderstanding?