Dynamically changing hierarchy in a table according to the told that logs in.

[johnmenx]

I have a table with the hierarchy A B C D E. And sales for each of these. Now A is the top level management, B is next level under A, etc.

So when an A logs in, he needs to see B, C, D, E with thier sales numbers in the table. And because RLS is applied he will only see the data for basically everyone in his team under him. 

If one of the Bs logs in, they see the table as C, D, E and if one of the Cs logs in they will see the table as D, E

For example:
if a regional manager logs in. He needs to see the table as the data for all the area managers under him and then he can drill down the hierarchy to go further.  If an area manager logs in instead, he needs to see the default view starting with the district managers.

Using RLS you can only filter the data that one sees, but you cannot set the default view with which the pages and the tables render. Is there any workaround for this?

[v-yangliu-msft · Accepted solution]

Hi  @johnmenx ,

I created some data:

Table1：

Table2：

Here are the steps you can follow：

1. Modeling – Manage roles – Create – Table filter Dax expression.

PATHCONTAINS('Table1'[Manager ID],
MAXX(
   FILTER(
        'Table1',[Email]=USERPRINCIPALNAME()),'Table1'[ID]))

2. View as roles.

3. Result:

Refer to :

Dynamic Row Level Security with Organizational Hierarchy Power BI - RADACAD

This is the related document, you can view this content:

Positions in Many-to-Many Relationship - Part 1 - RADACAD

Dynamic Row Level Security with Manager Level Access in Power BI - RADACAD

Best Regards,

Liu Yang

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