How to Expand Matrix in Power BI

The matrix visual in Power BI is a powerful way to summarize data, but its true strength lies in its drill-down capabilities. While a table shows data in neat rows and columns, a matrix lets you dig deeper into hierarchical information, like moving from product categories to sub-categories, and then to individual products. This guide will show you exactly how to control the expand and collapse features of a Power BI matrix to explore your data more efficiently.

We'll cover the basics of expanding single items, techniques for expanding entire levels at once, and even how to create dedicated "Expand All" and "Collapse All" buttons to give your report viewers a cleaner, more intuitive user experience.

Understanding the Power BI Matrix Hierarchy

Before we jump into the "how," it's important to understand the structure of a matrix. Unlike a simple table, a matrix visual is built on hierarchies. This structure is what allows you to "expand" a row to see the more granular data it contains.

In the Visualizations pane, when you drag multiple fields into the "Rows" well, you create a hierarchy. For instance, you might have:

• Country

• State/Province

• City

In this setup, "Country" is the top level of the hierarchy. When you expand a country, you'll see the states or provinces within it. When you expand a state, you'll see the cities within it. Having this mental model of parent-child relationships is the key to mastering the expand and collapse features.

Let's use a common sales data example where your row hierarchy is Category → Sub-Category → Product Name.

Manual Expand and Collapse Controls

Power BI gives you a few built-in ways to interact with your matrix directly on the report canvas. These are the most common methods you and your end-users will use for day-to-day data exploration.

1. Expanding One Row at a Time

This is the most straightforward method. When your matrix rows are collapsed, you'll see a small plus icon (+) next to each row header that contains another level of data.

• Click the + icon: Expands the selected row to show the next level of the hierarchy. For example, clicking "+" next to "Bikes" would reveal sub-categories like "Mountain Bikes," "Road Bikes," etc.

• Click the - icon: Collapses an expanded row, hiding the children and returning to the parent level.

This method is great for targeted exploration when you only want to investigate a specific item.

2. Expanding an Entire Level at Once

What if you want to see all sub-categories for every main category simultaneously? Clicking each plus icon individually would be tedious. Luckily, there’s a faster way.

1. Right-click on any of the row headers in the level you want to expand (e.g., right-click on "Clothing" or "Bikes").

2. From the context menu that appears, hover over "Expand."

3. Select "Entire level."

This action will expand every single item at that level of the hierarchy. To collapse them all, simply follow the same steps but select "Collapse" > "Entire level." This is a huge time-saver and one of the most useful context-menu features in Power BI.

3. Expanding All the Way to the Bottom

Sometimes you need to see the most granular level of detail for a specific category right away. Instead of expanding one level at a time (Category to Sub-Category, then Sub-Category to Product Name), you can expand all the way down at once.

1. Right-click on the specific parent row header you want to fully explore (e.g., right-click on "Bikes").

2. From the context menu, select "Expand" > "All."

This will reveal all descendant levels for that specific row only. "Bikes" will instantly expand to show both its Sub-Categories and all the underlying Product Names, saving you multiple clicks.

Customizing the Default Expand/Collapse State

By default, your matrix might load completely collapsed. However, you can change the default settings so that your viewers see certain levels already expanded when they open the report.

This is controlled in the Format visual section of the Visualizations pane:

1. Select your matrix visual.

2. Go to the Format visual tab (the paintbrush icon).

3. Expand the Row headers section.

4. Look for the +/- icons toggle. Make sure this is turned on, as this is what gives you the interactive expand/collapse functionality.

Further customization for the default state is often managed by saving the report in the desired configuration. If you expand a level and then save and publish the report, it will often default to that view for end-users visiting it for the first time.

Creating "Expand All" and "Collapse All" Buttons

For the best user experience, you can provide clear on-page buttons that allow viewers to expand or collapse the entire matrix with a single click. This is a more advanced technique that involves using Bookmarks.

Bookmarks in Power BI capture the current state of a report page, including filters, slicers, and the state of visuals (like a collapsed or expanded matrix).

Step 1: Create the "Collapsed" State and Bookmark

1. Make sure your matrix is fully collapsed. If it’s expanded, right-click a header and select Collapse > Entire level until all levels are collapsed.

2. Go to the View tab in the ribbon and open the Bookmarks pane.

3. Click the Add button in the Bookmarks pane. A new bookmark will appear.

4. Rename the bookmark to something clear, like "Matrix Collapsed."

Step 2: Create the "Expanded" State and Bookmark

1. Now, expand your matrix to the desired state. For a full expand, right-click a header and go to Expand > Entire level for each level you want to show. You can repeat this for all sub-levels as well.

2. Once the matrix is fully expanded, click the Add button in the Bookmarks pane again.

3. Rename this new bookmark to "Matrix Expanded."

Important Note: When you click the "... " next to each bookmark, you can control what it remembers. By default, it captures "Data," which includes the expand/collapse state. Make sure "Data" is checked. You may want to uncheck "Display" if you don't want the bookmark to affect which visuals are shown or hidden.

Step 3: Add Buttons and Link them to Bookmarks

1. Go to the Insert tab in the ribbon and select Buttons. Choose "Blank" for the most flexibility.

2. Add two buttons to your report canvas. Position them where you want, maybe above the matrix.

3. Select the first button. In the Format pane for that button, go to Style and add text to label it "Collapse All."

4. With the same button selected, go to the Action section in the Format pane. Turn the toggle On.

5. For Type, select "Bookmark."

6. For Bookmark, choose "Matrix Collapsed."

7. Now, repeat this process for the second button. Label it "Expand All" and assign its action to a "Bookmark" of type "Matrix Expanded."

That's it! Now, viewers can simply click your custom buttons to instantly toggle between a fully collapsed high-level summary and a fully expanded detailed view. This small addition makes your dashboards much more professional and user-friendly.

Final Thoughts

Mastering the Power BI matrix's expand and collapse features unlocks a deeper level of interactive data exploration. By moving beyond single clicks and utilizing the right-click menu and bookmarks, you can create much more dynamic and insightful reports for your audience, allowing them to move seamlessly from a high-level overview to granular detail.

While powerful, setting up these kinds of precise, interactive views in a tool like Power BI often involves clicking through nested menus and configuring bookmarks. We’ve found that many teams spend more time wrestling with their BI tool's interface than getting clear answers from their data. That's precisely why we built Graphed. Instead of navigating complex formatting panes, you can generate reports just by asking a question, like "show me a drill-down report of sales by region and city for this year." Our AI builds the live, interactive visuals you need on the spot, turning hours of setup into a 30-second task.