In this latest post, I’ll show you how to use the drill through functionality in Power BI to show a table of related records against one selected record that you ‘drill through’ against.
Related Tables
To work with this functionality, we’ll need some related data to implement this against. I’ve got a table of calls and a table of users where I’ve also got a many to many relationship between the two to produce a list of call participants.
2 Visuals
So in Power BI, I’m going to create two visuals, one for my calls, and one as my intersect table where I’ve expanded more details from my user table in my Power Query editor.
Above I’ve got my call table. I haven’t included my data but you can see the headers to get an idea of what I’m working with. On another screen in my report, I’m going to create the same kind of visual for my intersect table using my expanded columns from my user table to show the participants details.
Now that I’ve got my two visuals which are tables, I can create my link to allow drill through to work.
Here I’m going to create a scenario… I’m going to have what I’d like to call a ‘from’ table, and a ‘to’ table.
My from table is the table I’m coming from where I have to select a record to drill through against. And my to table is the table I drill through to, and that I navigate to which will show the related records, relating to the record I came from.
In this scenario my ‘from’ table is my Calls table, and my ‘to’ table is my Participants table which is the intersect between my calls and user tables.
Configure drill through
Now, I’m going to apply my drill through field to my ‘to’ table. This field has to be a field present in both tables creating my relationship, and so I’m going to use my call ID column.
I’m going to take the call ID column from my call table and place it in the drill through field of my ‘to’ table visual.
Drill through in action!
Now that we’ve configured drill through between our tables, all you need to do is head over to your ‘from’ table. Right click on a record, click ‘drill through’ and select your ‘to’ table page name.
Now you’ll have a view of the records in your ‘to’ table, related to the record in your from table!
I hope this post helped! If you didn’t understand this topic at all, or have any questions, let me know.