Dynamic RLS for multiple columns

[Anonymous]

Hello there!

I would like to ask you, how to apply Dynamic row-level security for 2 columns at the same time?

I have 3 data tables:

Table Sales

Owner	Manager	Sales	Year
Albert Wehr	Cody Lem	150	2020
Bernard Gripp	Mixael Crol	130,22	2020
Mixael Crol	Tommy Teller	56	2020

Table Owner

Owner	Username
Albert Wehr	albert.wehr
Bernard Gripp	bernard.gripp
Mixael Crol	mixael.crol

Table Manager

Manager	Username
Cody Lem	cody.lem
Mixael Crol	mixael.crol
Tommy Teller	tommy.teller

So users can see all rows in dataset Sales, where they are owners or managers. I created working DRLS for managers, but for owners it did not work, even though I did it the same way. 

Could you, please, advice me, how to make the DRLS for both columns? Maybe there is easier solution I don't know

Thank you in advance!

[jdbuchanan71]

My sample file behaves the way you want it to though correct?

[jdbuchanan71]

Sorry, I didn't see that the same person can be an owner or a manager.  In that case moving the manager as a new column in the owner table is your best bet.

I have attached my sample file for you to look at.

[Anonymous]

Hello @jdbuchanan71 , sorry for late response, unfortunately, did not help 😞 I still can see only rows where user is a manager 😞

[jdbuchanan71]

You tried making a second role?  You would assign the owners to one role and the managers to the other role and each role would only filter the one table it is related to.

[Anonymous]

I have made 2 roles: Manager and Owner. I filtered table Manager and Owner [Username] = USERNAME(), and made for both 2 roles filter in Sales = RELATED('Manager/Owner'[Manager/Owner])

[jdbuchanan71]

@Anonymous 

You would either need to make two roles, one for owners and one for managers so you could apply the filters separately or you could move the manager field to the owners table then use something like.

VAR _UPN = USERPRINCIPALNAME()
RETURN 
Owners[Owner] = _UPN || Owners[Manager] = _UPN

Right now I assume you added the Owners filter to an existing role that also filters the Managers.  No row in your sales table will have the same user for both the owner and the manager fields and RLS filters combine to only show the rows allowed.  I you are getting no results when the RLS is applied yes?

[Anonymous]

Well it didn't work unfortunately, tried to apply it for my dataset 😞

[Anonymous]

Well if I apply both 2 RLS, it works only for manager. Users will be given both 2 profiles: managers and owners, but can see only rows with their names. I will try your solution 🙂