RLS across different fields of a same table.

[WilliamA]

Hi All,

I have a table that has 4 fields

ID, Assigned_Person, Target_Person,Due_Date

I have 2 table visuals using the same table above however for the user logged in I want table 1 to use RLS based on the assigned_Person Field and table 2 to be filtered down based on the target_person field.

How can this be achieved?

I am thinking of loading in the original table twice and applying RLS on each - would this need to be 2 different roles or the same role with multiple conditions?

Any other suggestions on how this can be achieved?

[sjoerdvn · Accepted solution]

As I see this, this is not about Row Level Security, but about filterering visuals based on the logged in user. 
To do that, you would create some measures using functions like USERPRINCIPALNAME and use those in filters on the visuals. 
If the users have build authorization on the data, you would off course still need to implement RLS to avoid access to data they are not allowed to see.

[Anonymous · Accepted solution]

Hi,

Thanks for the solutions raji_n  and sjoerdvn offered, and i want to offer some more information for user to refer to.

hello @WilliamA , based on your descriotion, you can unpivot your table to the followign format in power query first.

Then create two table visual, one select the person_type is Assigned_Person, the other is Target_Person.

Then create a rls.

USERPRINCIPALNAME()='Table'[Person]

Then it can work.

Best Regards!

Yolo Zhu

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

[raji_n]

@WilliamA yes you can combine values of both the tables for implementing RLS

[sjoerdvn · Accepted solution]

As I see this, this is not about Row Level Security, but about filterering visuals based on the logged in user. 
To do that, you would create some measures using functions like USERPRINCIPALNAME and use those in filters on the visuals. 
If the users have build authorization on the data, you would off course still need to implement RLS to avoid access to data they are not allowed to see.