Forum Discussion
Matrix with Dynamic Rows Based on Slicer
Hi!
I have a hiearchy slicer and matrix. When I select nothing on the filter, I want the matrix to show Level 1. When I select a Level 1 item on the slicer, I want the matrix to dynamically update to show the Level 2 items under the Level 1 item selected.
Ex.
If the table below were my hierachy, I would have my slicer set up with Continent -> Country -> City.
If I select nothing in this slicer, I want the Matrix to show Rows
- Europe
- North America
- Africa
If I select Europe in the slicer, I want the Matrix rows to update to
- France
- England
| Continent (level 1) | Country (level 2) | City (level 3) |
| Europe | France | Paris |
| Europe | Engand | London |
| North America | United States | Chicago |
| North America | Canada | Montreal |
| Africa | South Africa | Cape Town |
Any help greatly appreciate! Thanks!
Hi abd8,
Apologies for the delay in getting back to you.
Power BI does not currently support using a hierarchy slicer to automatically move between levels in a matrix. A slicer always acts as a filter, so when a user selects something like Europe, Power BI filters the entire model to Europe it does not switch the matrix to the next level (e.g., France, England) or hide the current level. While this type of “level navigation” feels natural from a user perspective, the product simply isn’t designed to work that way today.
The practical alternatives are to rely on the built-in matrix drill-down, create separate views using bookmarks, or redesign the report layout to guide users through levels in a more step-by-step manner. These options allow you to provide a similar navigation experience, even though native slicer-driven level switching isn’t available yet.
Thank you for using the Microsoft Fabric Community Forum.
9 Replies
- Ashish_Mathur
Super User
Hi,
Isn't that standard built in PowerBi functionality. Just select Europe in the slicer and only 2 rows should show up in the matrix.
- xifeng_L
Hi abd8
You can try below solution :
The Aux Table's expression as below:
Auxiliary Table For X Axis = UNION( SELECTCOLUMNS(ALL('Table'[Continent]),"Item",'Table'[Continent],"Type","Continent"), SELECTCOLUMNS(ALL('Table'[Country]),"Item",'Table'[Country],"Type","Country") )The measure as below:
Measure = VAR FilterEnv = SUMMARIZE('Table','Table'[Continent],'Table'[Country]) RETURN IF( ISFILTERED('Table'[Continent]), CALCULATE( CALCULATE( SUM('Table'[Value]), TREATAS(VALUES('Auxiliary Table For X Axis'[Item]),'Table'[Country]), FilterEnv ), 'Auxiliary Table For X Axis'[Type]="Country" ), CALCULATE( CALCULATE( SUM('Table'[Value]), TREATAS(VALUES('Auxiliary Table For X Axis'[Item]),'Table'[Continent]) ), 'Auxiliary Table For X Axis'[Type]="Continent" ) )Any others details, pls refer to pbix file.
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
v-kpoloju-msftCommunity Support
Hi abd8,
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to xifeng_L, Ashish_Mathur, danextian, for those inputs on this thread. I reproduced the scenario, and it worked on my end. I used it as sample data and successfully implemented it.
outcome:
I am also including .pbix file for your better understanding, please have a look into it.
Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
- v-kpoloju-msft
Hi abd8,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
- v-kpoloju-msft
Hi abd8,
Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.Thank you.
