Dynamic row-level security with period reference

[marksettler]

Hello, this ist my first post and I have a problem with my dynamic row-level securits. In my example, the employee Jacob Young worked in the Sales department with Lisa Nguyen as manager until 29.02.2024 and has been working in the Finance department under the manager Michelle Scott since 01.03.2024. To make the data visible so that each employee only sees their own data and the managers see their employees' data, I use the PATH function in the user table and LOOKUPVALUE in the role. However, this means that only the current manager Michelle Scott sees all of Jacob Young's data and the manager Lisa Ngyuen does not see any data of him. My goal is that all managers see the data depending on the Org_from and Org_to time periods, which are either attached directly to the data record or are available separately in the User_History table. Unfortunately, I have no idea how to implement this. 

You can find my example file under this link and thank you for your help!

https://drive.google.com/drive/folders/1ezX3BesFvTvebl1yZXJtt3-E5-oB-ekB?usp=sharing

I added some screenshots. I'm Using the path function in the User table, it Returns a delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until current. In my DynamicRLS role i'm using the lookupvalue function to lookup the EmployeedID of the current logged in userprincipalname in the VAR and returning all data where the current user is contained in the path by using pathcontains function.

[marksettler · Accepted solution]

We found a solution, but we're not able to do the calculations in Power BI.

First we had to calculate the hierarchies with date reference to the individual data and create our own path in on our SQL server and added it to our individual data records as an additional column. Then we added each employee without data to our data table and used our lookupvalue/pathcontains role on our data table an not on the user table. This is much less convenient but works.

There is only one disadvantage to this method: If you're a department manager for only a few month you can't see the data for the past. This is because the path is calculated at the time of the creation date when you didn't have the job.

[v-jiewu-msft]

Hi @marksettler ,

Based on the knowledge, try to use the USERPRINCIPALNAME function. Please try the following method:

Firstly, define roles and rulers.

Then, select the table and apply a DAX rule.

You can also view the following documents to learn more about dynamic RLS.

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

USERNAME() v/s USERPRINCIPALNAME() in RLS for Pow... - Microsoft Fabric Community

Solved: Dynamic RLS with USERNAME() - Microsoft Fabric Community

Besides, I can’t open the link file.

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

You can refer the following links to share the required information.

How to provide sample data in the Power BI Forum

Best Regards,

Wisdom Wu

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

[marksettler]

Hi @v-jiewu-msft ,

thank you for your reply. Unfortunately the links don't help me. I have added a few screenshots and also changed the download link to google drive. I hope this clarifies things and the download link works.

Best regards