RLS for org hierarchy levels that displays aggregates for the lowest level

[Chris2016]

Hello,

I have to create row level security for different levels of management, where all the managers in the various levels see only their own data, but the bottom level (employee sales) is aggregates only.

To be more clear, the table is something like this:

Department Manager	Division Manager	Subdivision Manager	Employee	Sales
A@store.com	ab@store.com	abc@store.com	L	100
A@store.com	bc@store.com	cde@store.com	M	140
A@store.com	cd@store.com	efg@store.com	N	150
B@store.com	de@store.com	ghi@store.com	O	50
B@store.com	ef@store.com	ijk@store.com	P	15
B@store.com	fg@store.com	klm@store.com	Q	20
B@store.com	gh@store.com	mno@store.com	R	70
C@store.com	hi@store.com	opq@store.com	S	110
C@store.com	ij@store.com	qrs@store.com	T	90

The RLS should ensure that the mangers in any level of management see the data for their own directs, but only aggregates at employee level.

For example, Department manager B@store.com should see this, where #Sales is the sum of sales for all Employees reporting to them:

Division manager ab@store.com should see this:

Whereas a Subdivision manager such as mno@store.com should see this.

The Employees are not exposed in the visual, but all their sales aggregates should be visible to their managers in all the levels.

Any idea how to achieve this? Many thanks! 

[amitchandak · Accepted solution]

@Chris2016 , Try if this role can help . Test File attached after Signature 

[Department Manager] = USERPRINCIPALNAME() || [Division Manager] = USERPRINCIPALNAME() || [Subdivision Manager] =USERPRINCIPALNAME()

[amitchandak]

@Chris2016 , Check if this article from RADACAD can help 
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

[Chris2016]

Thanks a lot for the suggestion, I looked at the article, unfortunately my model has Levels of hierarchy and I don't know how to arrive at the view that Reza has in his demo - ID, Name and Manager ID. If you could help me with that, i would appreciate it very much. Thanks!

[amitchandak · Accepted solution]

@Chris2016 , Try if this role can help . Test File attached after Signature 

[Department Manager] = USERPRINCIPALNAME() || [Division Manager] = USERPRINCIPALNAME() || [Subdivision Manager] =USERPRINCIPALNAME()

[Chris2016 · Accepted solution]

Thanks so much, this also works, I also just solved it in a similar way, by using a mock Path:

_Concatenate = [CEO] & "|" & [Department Manager] & "|" & [Division Manager]  & "|" & [Subdivision Manager]  & "|" & [Employee]

Then add this in the table in Manage Roles:

PATHCONTAINS(
    'Table'[_Concatenate],
    USERPRINCIPALNAME()
)

Thanks so much for all your help!