Changing Hierarchy via User Input (with different number of hierarchy levels)

[Bahadurma]

Hello all, 

It is very hard to get rid of all private data from my .pbix file, so I will do my best to present my question in an understandable literal way.

I have 3 main tables; 1-Products , 2-Shops, 3-Transactions; I basically am reporting sales...

The main reporting template is with the following hiearchy:

Matrix visual Rows

Predefined hierarchy

Product[Category]

Product[SubCategory]

Product[Brand]

Product[Variant]

Product[Product]

Matrix visual columns

some measures

this works of course.

the trick is that in my products table, I have multiple definitions for products, some clients do not want to see matrix by my predefined hierarchy, they have their own and one may have 4 levels, another 7, whereas my original has 5. I can define multiple client-based hierarchies and then drag/drop them from my matrix visual based on the client. However the number is quite high and its very prone to manual errors.

Is there a way to define a slicer/dropdown on the visual and based on this inputs value, the "dynamic hierarchy" should update its contents and if its client 1, the matrix should have 4 levels of drill down depth, but for client 2 it should have 7 levels.

I oversimplified my problem, but at least there may be some starting point for me to work upon. I 'm open to new visuals instead of matrix or free/paid tools.

thanks for reading.

[v-rzhou-msft · Accepted solution]

Hi @Bahadurma ,

According to your statement, I think you want your report show different hierarchy levels for different clients. It is hard for us to achieve this in one visual. Here I suggest you to create visuals with different hierarchy levels in different Pages. And then hide all pages exclude home page. You can create a dynamic page navigation in this home page. Then your clients can see the page he has permission to by this page naviagtion.

In my sample there are four pages, Page 1/2/3 and Home Page. 

I can see Page 1 with a visual with 5 hierarchy levels, Client 1 can see Page 2 with a visual with 7 hierarchy levels, Client 2 can see Page 3 with a visual with 3 hierarchy levels.

Create a measure as below.

M_PageName = CALCULATE(MAX(User[PageName]),FILTER(User,User[USERPRINCIPALNAME] = USERPRINCIPALNAME()))

Then let's have a test by viewing the report as Client 1.

Ctrl + left click, user will be naviagted to  Page 2.

You may refer to this blog to learn more details about Dynamic Page Navigation Based on User Login

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.