Allow users to select different combinations of hierarchies in Matrix (MDX cube)

[Elena_Kalina]

Hello,

I have a matrix and the need for the possibility of choosing any sequence of the hierarchy of the matrix. I know that there is a great solution using Field Parametes. But my report is connected by MDX Cube, and because of this I cannot create parameters.

I tried to create bookmarks with matrices, where each bookmark contains a table with its own level order (the drawback is that when I expand a certain hierarchy level and switch to another bookmark, that level won’t stay expanded, as these are two unconnected matrices that don’t influence each other).

Also, I tried to use Power BI’s visual personalization and bookmarks (explaining the required steps to the client would necessitate training, which is inconvenient).

Perhaps you can help me find the best solution, I would be very grateful

[Akash_Varuna · Accepted solution]

Hi @Elena_Kalina  try syncing bookmarks with a single matrix to control hierarchy visibility. Use drillthrough pages for detailed breakdowns and slicers to let users select hierarchy levels dynamically. You can also allow users to expand/collapse levels within the matrix for manual control. You can also check for custom visuals from market place.

[v-venuppu · Accepted solution]

Hi @Elena_Kalina ,

This is not possible because Matrix visuals in Power BI do not share expand/collapse states across different visuals even if they look the same.

Bookmarks store the state of each visual separately. So:

Matrix A’s expand state is remembered in Bookmark A,

But Matrix B’s expand state in Bookmark B is independent.

Visuals behave in the same way as this is a limitation of the Power BI visual + MDX model architecture.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[v-venuppu]

Hi @Elena_Kalina ,

Thank you for the Response..!!

Here are steps that I have followed in attached .pbix file with sample data:

1.Created table called ViewSelector.

2.Created 3 Metrix visuals

Matrix 1 (View1): Rows = Segment > Category > SubCategory

Matrix 2 (View2): Rows = Segment > SubCategory > Category

Matrix 3 (View3): Rows = Segment only (or any layout you prefer)

For each matrix:

Add Total_Sales to Values.

3.Created 3 Bookmarks

4.Insert a Bookmark Navigator (Insert > Buttons > Navigator > Bookmark Navigator)

It automatically picks up the bookmarks you created.

Now, when users click on a view (e.g., View1, View2, etc.), the relevant matrix appears with its hierarchy while preserving expand/collapse states because only one matrix is active at a time.

As per my underestanding, I have created a pbix file with all the above steps applied.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Elena_Kalina]

Hi @v-venuppu 

Thank you very much for trying to help me and for taking the time to even create a test example. However, this solution is very similar to what I described when I initially asked the question, and unfortunately, your solution still doesn’t resolve the issue with the synchronization of expanded levels across different bookmarks—the problem I illustrated in the images below.

[v-venuppu]

Hi @Elena_Kalina ,

Since your report uses an MDX cube (which doesn’t support field parameters or dynamic hierarchies), here's a practical workaround using bookmarks and a slicer - all while keeping just one matrix visible at a time:

1.Create a Manual View Selector Table

a.Create a small table in Power BI(Home > Enter Data) with hierarchy options like:
Geography > Product, Product > Time, Time > Geography

b.No need to connect this to your cube - it's just for controlling view selection.

2.Create Multiple Matrix Visuals

a.Build one matrix for each hierarchy sequence you want.

b.Arrange fields manually in the correct order (drag them into Rows area).

c.Keep only one matrix visible at a time while setting up.

3.Create Bookmarks for Each View

a.Show only one matrix visual and hide the others.

b.Add a bookmark for each layout (e.g., Bookmark for "Geography > Product").

c.Repeat for each matrix.

4.Connect the Slicer or Buttons to Bookmarks

a.Either use buttons to trigger bookmarks, or sync bookmarks with the slicer selection using a bit of logic.

b.This way, users can select their desired view, and the matching matrix appears - without losing expand/collapse state, since it's always the same visual underneath.

• You can’t dynamically reorder fields inside a matrix with MDX.
• Calculated columns for hierarchy control aren’t possible here.
• Each view needs its own matrix, but switching between them is smooth if you use bookmarks.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Elena_Kalina]

Thank you, @v-venuppu , for continuing to try to help me. But I finally realized that I actually understood your first answer correctly. This approach had already been tried by me beforehand - you're suggesting a solution using slicers and buttons, while I implemented it using buttons and slicers. But both my method and your proposed one have the same drawback that I mentioned from the very beginning: the expansion levels of the matrix aren't synchronized across different bookmarks. That is, if I expand a level on one bookmark, it will remain collapsed on another bookmark (unless the user previously expanded it there). The option of setting all matrix levels as expanded on all bookmarks isn't suitable either, as it would interfere with data analysis.

But I need this matrix view when I select another bookmark

[v-venuppu]

Hi @Elena_Kalina ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Akash_Varuna for the prompt response.

You can Use a single matrix + synced bookmarks + selection slicer
Here’s how:

1.Create a slicer with hierarchy view options

Create a table manually in Power BI like this:

ViewName
View 1
View 2
View 3
No need to connect this table to your data model.

2.Place a matrix visual on your page

Use DAX logic to dynamically swap the hierarchy fields based on the selected slicer value (more below).

3.Use DAX to switch columns
Since you can’t use Field Parameters, you’ll need to create a calculated column or measure that reacts to the slicer and chooses the right field to display.

Unfortunately, MDX cubes don’t support all DAX features, so here’s a workaround:

• You can create calculated measures for each hierarchy layout in the cube (if allowed) and use bookmarks to show/hide layers accordingly.

4.Sync bookmarks with the slicer

• Set up a bookmark for each hierarchy layout (all using the same matrix visual, just updating which fields are shown).
• Then use buttons or slicer to trigger the correct bookmark.
• This way, you don’t have to create multiple matrix visuals, and users won’t lose their expand/collapse state.

This does require a bit of setup, and the approach will depend on whether your MDX source allows conditional measures or calculated members.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Elena_Kalina]

Thank @v-venuppu  for your willingness to help me. I have a few questions. About the second and third points—in an MDX cube, we can create calculated measures for measures, but not for hierarchies. As for the fourth option, I don’t fully understand how I can use the same matrix but control which fields should be visible in bookmarks, since the sequences will be different, but the fields will remain the same. Could you explain this solution in more detail?

[Akash_Varuna · Accepted solution]

Hi @Elena_Kalina  try syncing bookmarks with a single matrix to control hierarchy visibility. Use drillthrough pages for detailed breakdowns and slicers to let users select hierarchy levels dynamically. You can also allow users to expand/collapse levels within the matrix for manual control. You can also check for custom visuals from market place.

[Elena_Kalina]

Thank you,@Akash_Varuna, for your suggestions.

Could you please elaborate on how to implement syncing bookmarks with a single matrix to control hierarchy visibility? This is new to me, and I haven’t worked with this approach before.

The drill-through option won’t work for us because we’d still run into the issue of creating field parameters that need to be added to slicers.

As for custom visuals from the marketplace, unfortunately, we couldn’t find anything suitable for our needs.