Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
Solved! Go to 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.
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
@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?
@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?
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.
Hi,
You need not check the individual boxes there. You may just type Alice in the search box.
In Excel that is possible. In Power BI slicer you still have to select the individual boxes, correct?
Yes, i think so.
@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?
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]))
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.
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
@Anonymous Hmm.... Not seeing how that works. See "distinct" PBIX file here - maybe I am misunderstanding?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
106 | |
75 | |
44 | |
39 | |
33 |
User | Count |
---|---|
165 | |
90 | |
65 | |
46 | |
43 |