Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Bare
Frequent Visitor

Multilevel RLS with userprincipalname

Hi All,

 

Can anyone help with pointing me in the right direction towards extending RLS beyond what I'm allowed to view?

 

Scenario:

So, I have the following structure:

 

organizational structureorganizational structure

Now I was able to set up RLS for a user to view hers and immediate direct employee reporting to her (for eample Emp B can view Emp B, Emp C & Emp D). I achieved this with an OR statement using 'userprincipalname()' in the 'Manage Roles' section (employee ID = userprincipalname() || manager ID = userprincipalname() or the employee dimension table. Works fine.

 

Challenge:

My challenge is, still using Emp B example, I want to be able to view next levels as well. That is, to be able to view results of Emp E, Emp F & Emp G via drill through if I so wish (?).

 

I guess the question is, is this even possible with plain vanilla RLS? In the employee dimension table I have created columns using the PATH functionalities believing therein may be the answer but I'm stuck.

 

Capture.JPG

 

In a nutshell, as an employee being able to view data at my level & further down the hierarchy chain from my level. Is there another way other than RLS?

 

Can you help?

Many thanks for your time

 

Regards,

Bare

 

PS I couldn't attach a sample file. Happy to do so if I figure out how Smiley Embarassed

 

 

 

5 REPLIES 5
Bare
Frequent Visitor

@avanderschildenI wanted to share update (and solution) on this issue.

 

By accident I came across a solution the fits nicely to what I was looking for. I found it in a post from Radacad:

 

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

 

Again, many thanks for helping out.

 

Cheers

avanderschilden
Resolver I
Resolver I

Hello,

 

You need a table with unique employees and their email/login. This will be the starting point of the RLS filter;

 

Emp1 | emp1@company.com

Emp2 | emp2@company.com 
Emp3 | emp3@company.com
Emp4 | emp4@company.com

 

The second table you need is a table with employees and their children, or parents and all employees they have access too;

 

Emp1 | Emp1

Emp1 | Emp2

Emp1 | Emp3

Emp2 | Emp3
Emp2 | Emp4

 

Now, Emp1 will filter the second table and only filter out Emp1, Emp2 and Emp3. No access to Emp4.

 

The last table is your transaction table with sales or whatever, and this will be connected to table 2 based on employee;

 

2019-01-01 | Emp2 | 50 Sales

2019-02-02 | Emp4 | 100 Sales

 

Now, Emp1, will only see a total number of 50 sales, since there is no access to the second row of the transaction table.

 

Hope this helps to model your data in the right way.

 

Regards,

Adrian

 

Emp1 | 

Thanks for your quick response @avanderschilden 

 

This is exactly where I got stuck: Emp 1 in your use can see Emps 2 & 3.

 

But because Emp 4 reports to Emp 2 who in turn reports to Emp 1, I want Emp 1 to be able to see figures for Emp 4 as well. Kind of like a waterfall RLS from the top node to the most bottom node in that chain for Emp 1

 

Regards,

Bare

So why dont you just add a row with;

Emp1 | Emp4

In that case you have what you need right?

You just have to store your table with parents and childs different, without level. Just 2 columns with a parents and child.

@avanderschilden  It's a good idea worth trying. I was avoiding any manual maintenance (the employee file comes from a separate system) that I didn't think of this. I'll simply carve out a 2 column solution with Power Query and let you know how it goes.

 

Many thanks

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.