Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hannats
Advocate II
Advocate II

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
Anonymous
Not applicable

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.

View solution in original post

16 REPLIES 16
ChandeepChhabra
Impactful Individual
Impactful Individual

@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

 

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

Hi @hannats,

 

Did you try my solution?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. image.png

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Yes, i think so.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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?

@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]))

 

Capture.PNG

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

@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

 

image.pngimage.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.