Hierarchy Row Level security with cascading functionality

[kalwabharath44]

Hi All,

I have a report which requires Hierarchy Row Level security with cascading functionality
L1 - Director
L2 - Manager
L3 - Supervisor
L4 - Regular user

When an L1 loges in then he needs to see all data corresponding to his L2, L3 and L4 and should not see other L1 Data. Same thing applies for L2,L3 and L4.

How do i implement this functionality, i know that we can create four roles(Ex: [L1 Email] = userprincipalname() ) and assign the users to each role individually but coming to L4 we are talking around 200 users (Adding them manually to the Role group is tedious process)

The question is if i create four Roles in the report and add users manually for the first time (Few users for L1 L2 and L3) how do we maintain the increase of users when there is a new data added and new users using the report. Is there a way to automate the process of adding the users to the Role group in the web version of the report?

[menphis21]

Hello @kalwabharath44 , @selimovd,

Finally, there is a solution ? i am facing the same issue

Thank you

[selimovd]

Hello @kalwabharath44 ,

in general, I would do that with Active Directory groups if that is possible.

So you would only have to map the AD groups to the roles, if a new user is joining the workspace you have to add him to the correct AD group. This could be part of the workflow to gain access.

If you need any help please let me know.
If I answered your question I would be happy if you give it a thumbs up 👍!

Best regards
Denis

[kalwabharath44]

Thank you for the response Denis. Also, i have an excel file with L1 L2 L3 and L4 data. can you please guide me on how to build the security model?

[selimovd]

That depends a lot on the Excel file. Do you have a PowerPivot model or do you "only" have the data in tables? How would you provide the Excel file to your users? What are your data sources?

For example if you do a live connection to an SSAS Tabular with PowerPivot in Excel the security from the SSAS works perfectly fine. If you do a direct query connection to a relational database also the security on the database works.

So it really depends.

[kalwabharath44]

Thankyou for the Response Denis. The data is in excel file as a regular sheet and i am pulling that into PowerBI.

Example or online sources have examples where ther are building the heirarchy but whereas i have the data as shown below.

User Email > Supervisor Email > L1 Email > L2 Email > L3 Email > L4 Email

How do i build dynamic row level security based on the data i have.