Creating a drill down using a matrix?

[Florie]

Hi. I'm trying to create a visual to show for each person ID their addresses so it looks something like this (excel example): 

So a user can click on the person ID and the addresses appear. I'm sure it's simple to do but I can't figure out what visual to use. I think it's a matrix but can't work out what to do. Thank you!

[Nasif_Azam · Accepted solution]

Hey @Florie ,

Yes, you're absolutely right a Matrix visual in Power BI is the perfect way to replicate this kind of expandable structure where users can drill down from Person ID to Placement ID and see details like address and dates.

Here’s how to do it:

Create a Drill Down Matrix in Power BI

1. Load Your Data

Ensure your Excel data is imported into Power BI. The data should be in a tabular format with headers like:

• Person ID
• Placement ID
• Address
• Placement Start Date
• Placement End Date

If Address is split across columns, consider merging them in Power Query (Transform Data).

2. Insert a Matrix Visual

1. In Power BI Desktop, go to the Visualizations pane.

2. Select the Matrix visual (it looks like a grid).

3. Set Up Hierarchy

Drag the following fields into the Rows section in this order:

• Person ID
• Placement ID
• Address (optional)

Then drag the following into the Values section:

• Placement Start Date
• Placement End Date

This will allow the matrix to group by Person ID, and when expanded, show the placement details.

4. Enable Drill Down

Make sure:

• The row headers have the small expand/collapse (+/-) icons.

• You enable drill mode (a downward arrow icon above the matrix) if you want to use clickable drill-through.

5. Format the Matrix (Optional but Recommended)

• Turn on Stepped Layout (or off, if you prefer indentation).

• Adjust word wrap for long addresses.

• Use conditional formatting or row styles for visual clarity.

Things to remember

• If you want a cleaner layout, you can combine Address with Placement ID in Power Query or a DAX calculated column.

• If a placement has multiple addresses or overlapping dates, ensure your data model handles it appropriately.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Florie]

Thank you. The +/- icon is toggled on but I can't work out how to set-up the visual for the data to appear as I'd like it to. This is how it looks at the moment. The address doesn't show but there is an address in the total row. I don't know how to lay it out in the Build section?

Many thanks

[PawelWrona]

Have you created a measure to display the address or this is simply the field? If meausre, please provide the code. If it's a field, please right click on it in the Build panel and see if there is any aggregation selected.

[andrewsommer]

Yes, you would use the Matrix visual and you will need to go to Format Your Visual > Row Headers > +/_ Icons and toggle them on

Please mark this post as a solution if it helps you. Appreciate Kudos.