RLS with multiple roles - Slicers not affected

[spv]

Hi everyone,

I am kind of new to RLS but I have read a lot about it.

I am working on a report and want to limit the values that a user can see. The user should see only data for themselves or data for employees reporting to them. This is the dynamic RLS with Organizational Hierrarchy, which I have implemented successfully.

However, I want ot add one more role where I will manually give access for specific users to see data for specific Business Units. For this, I have created an excel file which I input in the Power BI report. Below the format with dummy data can be seen:

Employee	Email	Region	Subregion	Business Unit	Key
A	a@mail.com	1	1	A	11
A	a@mail.com	1	2	B	12
A	a@mail.com	2	1	C	13
B	b@mail.com	1	1	A	11

I created a Buffer Table where I implement the new Role with definition:

'BufferTable'[Key] = LOOKUPVALUE('SecurityTable'[Key], 'SecurityTable'[Email], USERPRINCIPALNAME(), 'SecurityTable'[Key], 'BufferTable'[Key])

After that I connected the Buffer Table with my Business Unit Table with a Single directional relationship.

With the two above roles, the user can only see either the data about themselves and the employees below them or the data I have given them access manually. However. the slicers using columns from the Business Unit Table are not affected and the user can see every option. How could I fix that?

[v-yingjl]

Hi @spv ,

Not certain what is your expeceted output. If you want to get specific user data, just define the role like:

[Email] = USERPRINCIPALNAME()

When you view as a@mail.com, the slicer will also be filtered.

How did you create a buffer table and what did the key field represent..

If the above is not helpful, perhaps need more details about this issue for further discussion.

Best Regards,
Yingjie Li

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

[spv]

Hi,

Thank you for your message.

What you are describing is a simple RLS, right?

This has been already implemented in the report but on the Employee Dimension Table. Now I am trying to implement an extra role so that the user that is in the Security Table can see all the business units that are assigned to him in the table. I did an [Email] = USERPRINCIPALNAME() on the Security Table and then 'BufferTable'[Key] = LOOKUPVALUE('SecurityTable'[Key], 'SecurityTable'[Email], USERPRINCIPALNAME(), 'SecurityTable'[Key], 'BufferTable'[Key]). The Key is a unique key for each Business Unit which is then connected to the Business Unit Dimension Table from where I use the Region, Country, Business Unit as slicers. 

The issue is that the slicers are not affected by the Role and present every option even though the user can only see the data we want. This is not that 'nice' as a user experience.  

[StephenBard]

Did you ever firgue this out. I am having the exact same issue.

[amitchandak]

@spv , see these can help for Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/