Hello
I often produce reports for events we've held. The records for Event and Event Participations (two separate tables) are held in our CRM with a hierarchy between the event itself, the sessions linked to the event and the times within that session.
For example (top to bottom):
Parent Event = Event 1
Parent Session = Event 1 - Session 1
Child Session = Event 1 - Session 1 - 9:00
The Event Participations table would then list each record as the name of the event/session/time of session, their Participation ID and their Participation Status (Registered, Cancelled, Attended etc.).
(Event Type is a custom column that I create and there is a many to one relationship between the Event Name of each table)
| Event Name | Event Type |
| Event 1 | Parent Event |
| Session 1 | Parent Session |
| Session 2 | Parent Session |
| Session 3 | Parent Session |
| Session 1, Child 1 | Child Session |
| Session 1, Child 2 | Child Session |
| Session 1, Child 3 | Child Session |
| Session 2, Child 1 | Child Session |
| Session 2, Child 2 | Child Session |
| Session 2, Child 3 | Child Session |
| Session 3, Child 1 | Child Session |
| Session 3, Child 2 | Child Session |
| Session 3, Child 3 | Child Session |
| Contact ID | Participation ID | Event Name | Participation Status |
| C-C994jn483vz | C994jn483vz | Event 1 | Attended |
| C-B279pz518pD | B279pz518pD | Event 1 | Registered |
| C-D678lk722wu | D678lk722wu | Event 1 | Registered |
| C-C293tT566tb | C293tT566tb | Event 1 | Cancelled |
| C-D627mf646lC | D627mf646lC | Event 1 | Attended |
| C-C994jn483vz | C994jn483vz | Session 1 | Registered |
| C-B279pz518pD | B279pz518pD | Session 1 | Registered |
| C-D678lk722wu | D678lk722wu | Session 1 | Registered |
| C-C293tT566tb | C293tT566tb | Session 3 | Attended |
| C-D627mf646lC | D627mf646lC | Session 1 | Attended |
| C-C994jn483vz | C994jn483vz | Session 1, Child 1 | Registered |
| C-B279pz518pD | B279pz518pD | Session 1, Child 1 | Cancelled |
| C-D678lk722wu | D678lk722wu | Session 1, Child 2 | Registered |
| C-C293tT566tb | C293tT566tb | Session 3, Child 2 | Cancelled |
| C-D627mf646lC | D627mf646lC | Session 1, Child 3 | Cancelled |
What I want to be able to do is have three table visuals - one for Parent Event, one for Parent Session and one for Child Session - and have them set up in a way that allows users to click/interact with each row in the table and show the corrosponding overarching event/session/session times. For example, if they click Session 1 within the Parent Session table, the Parent Event table would show the Parent Event, the Parent Session would show the session that has been clicked and the Child Session would show the times within that specific session.
Table 1:
| Parent Event | Event Participations |
| Event 1 | 49 |
Table 2:
| Parent Session | Session Participations |
| Session 1 | 19 |
| Session 2 | 12 |
| Session 3 | 18 |
Table 3:
| Child Session | Child Participations |
| Session 1, Child 1 | 10 |
| Session 1, Child 2 | 4 |
| Session 1, Child 3 | 5 |
| Session 2, Child 1 | 5 |
| Session 2, Child 2 | 5 |
| Session 2, Child 3 | 2 |
| Session 3, Child 1 | 5 |
| Session 3, Child 2 | 5 |
| Session 3, Child 3 | 8 |
I've attempted a few ways to do this, but the closest i've got is only being able to show the relationship one way (from the top of the hierarchy to the bottom) by using three separate columns for each event type (shown below), and using each corrosponding column in the tables, rather than the Event Name.
| Event Name | Event Type | Parent Event | Parent Session | Child Session |
| Event 1 | Parent Event | Event 1 | | |
| Session 1 | Parent Session | Event 1 | Session 1 | |
| Session 2 | Parent Session | Event 1 | Session 2 | |
| Session 3 | Parent Session | Event 1 | Session 3 | |
| Session 1, Child 1 | Child Session | Event 1 | Session 1 | Session 1, Child 1 |
| Session 1, Child 2 | Child Session | Event 1 | Session 1 | Session 1, Child 2 |
| Session 1, Child 3 | Child Session | Event 1 | Session 1 | Session 1, Child 3 |
| Session 2, Child 1 | Child Session | Event 1 | Session 2 | Session 2, Child 1 |
| Session 2, Child 2 | Child Session | Event 1 | Session 2 | Session 2, Child 2 |
| Session 2, Child 3 | Child Session | Event 1 | Session 2 | Session 2, Child 3 |
| Session 3, Child 1 | Child Session | Event 1 | Session 3 | Session 3, Child 1 |
| Session 3, Child 2 | Child Session | Event 1 | Session 3 | Session 3, Child 2 |
| Session 3, Child 3 | Child Session | Event 1 | Session 3 | Session 3, Child 3 |
I've produced a test pbix to demonstrate this, and this is shown on page 'Test 3' of the below document (please download).
Event Hierarchy Test.pbix
I understand to an extent why the visuals don't interact in the way I want, but I'm struggling to find a way to manipulate the data/relationships/measures to show what I want. Any insight into how I can achieve the interactions working in the way I'm aiming for would be much appreciated. Please let me know if any more information is needed.
