Creating a drill down using a matrix?

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