Displaying related data when data should be filtered out
I have what feels like should be a simple problem, but for some reason I've got a mental block against it and would love to get input from the community on how to solve it. Basically, I want to show data in either a straight table or pivot table that is all textual, as it pertains to one particular record.
Here's the essentials of my data model ([Attendance] is the fact table):
People attend an event (one event per year), and they are seated at a given table. Each table would likely have 8-12 people seated at it. Given that, here's a mockup of what I'd like to accomplish:
The user would use the filter pane on the left to select ONE person (filtering on [Full Name]), which would then show the details for this particular individual (more elements are on the screen than in this mockup, but the mockup has the parts I'm concerned with). The table on the left would be great to be a pivot table, with the last column showing all of the people that were seated at the same table as that person in the same year. The table on the left is similar data, showing how often they've sat with the same people before.
I've tried all sorts of things trying to get this to work but just can't seem to come up with the right combination of set analysis expressions, weird aggregation functions, etc., and have just hit a wall.
Would love any help and pointers; thanks in advance!