咨询：实现Power BI层级嵌套指示器匹配Power Pivot功能的方案

Hey there! I’ve run into exactly this scenario with several clients who were dead set on keeping that familiar +/- expand/collapse behavior they loved in Excel’s Power Pivot. Power BI’s approach to hierarchies is different, but there are solid workarounds to get that same feel. Let’s dive in:

1. Use the Matrix Visual’s Native Hierarchy Controls #

This is the closest out-of-the-box solution to Power Pivot’s behavior, and it’s often overlooked because of how you set it up:

• First, make sure your data model has properly defined hierarchies (go to the Model tab, right-click your table, select New Hierarchy, then add your nested levels in order).
• Drag the entire hierarchy into the Rows bucket of the Matrix visual—don’t add individual columns one by one. This triggers the +/- icons automatically for each level.
• Tweak the look to match Power Pivot: Under the Format pane > Row headers, enable Show expand/collapse buttons, adjust indentation to match their old view, and set the default expansion state (fully collapsed, expanded, or custom) under Default expansion.

2. Build Custom Expand/Collapse with Bookmarks & Buttons #

If the native matrix isn’t flexible enough (e.g., your client wants to expand specific sections without affecting others), you can create a tailored solution:

• Create calculated columns or measures to toggle visibility of each hierarchy level. For example:

  ShowLevel2 = IF(ISFILTERED('YourTable'[Level1]), 1, 0)
  

• Set up bookmarks that adjust visual filters to show/hide different levels (e.g., one bookmark for expanded Level 1, another for collapsed).
• Add buttons styled like +/- icons (use Unicode characters +/- or small custom images) and link each button to the corresponding bookmark. You can even set up bookmark actions to switch the button’s icon when clicked (e.g., from + to - after expanding).

3. Enhance Navigation with DAX Drill Paths #

For a more dynamic experience that mirrors Power Pivot’s intuitive navigation, use DAX to create a drillable path:

• Add a calculated column that concatenates your hierarchy levels:

  HierarchyPath = 'YourTable'[Region] & " > " & 'YourTable'[District] & " > " & 'YourTable'[Store]
  

• Pair this with a slicer using the HierarchyPath column, and create a measure that filters your visual based on the selected segment. Combine this with the matrix’s native +/- controls to give users two ways to navigate: clicking icons or selecting from the slicer.

Pro Tip for Client Buy-In #

Don’t skip showing the native matrix controls first! Many users don’t realize Power BI has this functionality—it just requires properly setting up hierarchies in the data model. A quick demo of how the +/- icons work and how to adjust the layout can often win them over without needing custom solutions.

内容的提问来源于stack exchange，提问作者Doug