topic Re: Multilevel RLS with userprincipalname in DAX Commands and Tips

Multilevel RLS with userprincipalname

Bare — Sun, 18 Aug 2019 22:40:38 GMT

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:

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.

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 :smileyembarrassed:

Re: Multilevel RLS with userprincipalname

avanderschilden — Mon, 19 Aug 2019 09:09:22 GMT

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 |

Re: Multilevel RLS with userprincipalname

Bare — Mon, 19 Aug 2019 15:22:53 GMT

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

Re: Multilevel RLS with userprincipalname

avanderschilden — Mon, 19 Aug 2019 15:37:10 GMT

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.

Re: Multilevel RLS with userprincipalname

Bare — Mon, 19 Aug 2019 21:13:29 GMT

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

Re: Multilevel RLS with userprincipalname

Bare — Wed, 02 Oct 2019 18:31:42 GMT

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