Row Level Security where report users should see their own data against table aggregates

[Chris2016]

Hi,

I have to create RLS for a table that has users and team leads:

User	Lead	Result	Status
A	X	1	Engaged
A	X	2	Engaged
A	X	0	Engaged
A	X	1	Engaged
B		2	Not Engaged
B		3	Not Engaged
B		0	Not Engaged
B		1	Not Engaged
C		0	Not Engaged
C		2	Not Engaged
C		4	Not Engaged
C		3	Not Engaged
D		0	Not Engaged
D		2	Not Engaged
D		1	Not Engaged
E	Y	0	Engaged
E	Y	1	Engaged
F	Z	2	Engaged

The requirement is that team leads who exist in the table (aka X, Y Z in column Lead) should see only their own Users but also the aggregate results of people with "Not Engaged" in Status. The purpose is to compare the results of their own users against the results of those "not engaged".
This is what the average results look like with no RLS in place:

This is what it should look like with the expected RLS in place, where the user who logs in the report is Lead X:

Column "Lead" in my table is the actual USERPRINCIPALNAME() with which the team lead will log in to open the report.

Any idea how this can be done?

Many thanks!

[Chris2016 · Accepted solution]

The answer was pretty simple, I just created an OR condition using both fields:

[Status] = "Not Engaged" || [Lead] = userprincipalname()

Thanks!

[Chris2016]

Thanks a lot, Binbin, I have done something similar to your suggestion, I created the RLS on the table as:

[Lead] = userprincipalname() || [Lead] = blank()

But in my table there are users who are "Engaged", who do not have a manager specified, and I don't want them showing up in the report when RLS is applied.

User	Lead	Result	Status
A	X	1	Engaged
A	X	2	Engaged
A	X	0	Engaged
A	X	1	Engaged
B		2	Not Engaged
B		3	Not Engaged
B		0	Not Engaged
B		1	Not Engaged
C		0	Not Engaged
C		2	Not Engaged
C		4	Not Engaged
C		3	Not Engaged
D		0	Not Engaged
D		2	Not Engaged
D		1	Not Engaged
E	Y	0	Engaged
E	Y	1	Engaged
F	Z	2	Engaged
G		3	Engaged
H		2	Engaged
I		1	Engaged

When I apply RLS, Lead X will say - Hey, I don't have 4 users who are engaged in the program, I only have 1:

I need to be able to use the Engaged Status in the RLS to exlude from the view those users who are engaged but do not have a manager. Thanks for any suggestions!

[v-binbinyu-msft]

Hi @Chris2016 ,

Please try below steps:

1. below is my test table

Table:

Table2:

Table 2 = VALUES('Table'[Lead])

Model:

2. create RLS role for Table 2

var _a=USERPRINCIPALNAME()
return
[Lead]=_a||[Lead]=""

 

3. add a slicer with Table 2 field, add table visual with Table fields

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.