Forum Discussion
Multiple values in cell - use as filter
Hello, I am trying to develop a dashboard of the registration history of a training program, for which all data has been historically kept in a spreadsheet. There is a single entry for each student registered, with various information about them and their course history on the same line. Where I am having a problem is that each student is paired with a mentor, but in some cases there are two mentors. I would like to be able to create a filter that allows me to see all students assigned to a given mentor, even if that mentor is co-mentoring a student. I am attaching some sample data to show how this ends up looking in the table.
My first instinct was to have a lookup table listing the mentors, and then to use Power Query to make a new table, referencing my main table, that used the split column function to make one row for each student-mentor relationship (so, some students were listed twice, once for each mentor), but this fails to filter the main table, even though the relationship is based on unique student IDs. A screenshot of the relationship between the three tables is included below.
Hi krdavies
Keep that split table you made where you broke out each student-mentor combo into separate rows. and follow rthses steps:
Change how the relationship works:
- Open Model view
- Click the line between "Mentor relationships" and "Registrant History"
- Set Cross filter direction to Both
Add your slicer:
- Drop the MENTOR field into a slicer visual
- Pick a mentor and it'll pull up everyone they work with
Basically the filter needs to work both ways, right now it only goes one direction, so selecting a mentor doesn't push back to your main table.
If that doesn't fix it, try changing the relationship type to Many-to-Many, sometimes Power BI gets picky about cardinality when you've got duplicate IDs floating around.
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡
3 Replies
Hi krdavies
Keep that split table you made where you broke out each student-mentor combo into separate rows. and follow rthses steps:
Change how the relationship works:
- Open Model view
- Click the line between "Mentor relationships" and "Registrant History"
- Set Cross filter direction to Both
Add your slicer:
- Drop the MENTOR field into a slicer visual
- Pick a mentor and it'll pull up everyone they work with
Basically the filter needs to work both ways, right now it only goes one direction, so selecting a mentor doesn't push back to your main table.
If that doesn't fix it, try changing the relationship type to Many-to-Many, sometimes Power BI gets picky about cardinality when you've got duplicate IDs floating around.
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡Thank you, this worked perfectly!
Glad it worked, I am happy for you
