cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hannats Regular Visitor
Regular Visitor

Return Other Records for Slicer Value Too

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Return Other Records for Slicer Value Too

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


16 REPLIES 16
Super User
Super User

Re: Return Other Records for Slicer Value Too

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Super User
Super User

Re: Return Other Records for Slicer Value Too

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.

Re: Return Other Records for Slicer Value Too

@hannats

 

Try this measure

 

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

 

Capture.PNG

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

 

Hope it helps, Thanks

 

hannats Regular Visitor
Regular Visitor

Re: Return Other Records for Slicer Value Too

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

hannats Regular Visitor
Regular Visitor

Re: Return Other Records for Slicer Value Too

@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.

Re: Return Other Records for Slicer Value Too

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

hannats Regular Visitor
Regular Visitor

Re: Return Other Records for Slicer Value Too

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?

Super User
Super User

Re: Return Other Records for Slicer Value Too

Hi @hannats,

 

Did you try my solution?

hannats Regular Visitor
Regular Visitor

Re: Return Other Records for Slicer Value Too

@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.