Matrix with Dynamic Rows Based on Slicer

[abd8]

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!

[v-kpoloju-msft]

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.

[abd8]

Thanks for the reply! I think I was unclear in my original question. I don't want it to just drill down. I want the table to dynamically update to show one level below what I select in the slicer as my top level.

So if I select Europe in the Slicer, Europe would no longer show on the table. It would dynamically update to the next level down and show France and England.

[v-kpoloju-msft · Accepted solution]

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.

[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.

[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~

[Ashish_Mathur]

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.

[danextian]

Hi @abd8 

This might be what you're looking for. The sample is not for matrix but it auto drills down.