Override Row Level Security

[careisin60]

I have a report that I succussfully added a dynamic row level security to it.  The purpose was to make it convenient for users to see only their own transactions without having to filter for it.  However, it is OK for the user to see all users' transactions.  Some managers want to look at everyone to see how their reports are doing compared to others.  I'd like to accomplish one of two possibilities.

1) Have row level security apply to a single tab in my report, and not in the rest.  As I understand it, that is not a feature in Power BI and is not currently planned.  Unless that changed, this is not an option.

2) Is there a way for the usuer to change their role or somehow override their security.  I know I can do this in desktop to test the RLS feature, but if the user had a way to change their role so they saw all transactions would work. 

[careisin60]

Here are some pictures to help, hopefully.  Let's say this is my table of information:

So far, I've been able to make the Row Level security to be dynamic with the IF functions used above.  So if Gabriel Stokes, a sales person, opens the report he would see this:

If Maggie Green, a sales manager opens the report, he would see this:

If Daryl Dixon, a vice president, opens the report, he see's this:

Where I'm having trouble is that I'd like to find a way that the user can shut off or do something to be able to see all the data.  I've tried puting conditional clauses using slicers in the RLS function, but it doesn't seem to work.  I don't know if there's a method to do this.  Is there coding I could put in the RLS function above to give the user power to change which column to filter for RLS? I want the user from sales people to vice presidents to be able to do something, like click a slicer or anything so they could see all the data like this:

[v-yetao1-msft]

Hi @careisin60 

The personnel structure of your RLS sounds a bit complicated. Can you use simple data or tables to reflect the data model you want to express and the results you want to achieve ?

Best Regard

Community Support Team _ Ailsa Tao

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

[careisin60]

To add more detail, I have a dynamic RLS process going on.  I have three columns.  Team Member, Team Manager, Regional Manager. 

Every individual in the Team Table has a column with their unique user principal name in Team_Member.  FOr the Team_Manager column, every persons' team manager's user principal name is listed, and for everyone they have their Regional Manager's user principal name in the Regional_Manager Column.

The RLS function had an if statement.

IF ( countrows(FILTER(ALL(Team),Regional_Manager = userprincipalname()))>0, Regional_Manager = userprincipalname(),

IF(countrows(FILTER(ALL(Team),Team_Manager = userprincipalname()))>0,Team_Manager = userprincipalname(),

Team_Member = userprincipalname())))

This was working.  When I tested the roles a team manager had all their direct reports showing, Regional Managers had all their team managers and direct reports and so on.

I added a column to my Team table, called All, and it has the same value for every row, "all@company.com".

I thought maybe I could add a slicer that would override the individuals user principal name to see everything.

I added another table called Viewer, with one column, called Viewer with two values, All or Individual.

I put a slicer in my report where you had to pick one of those two values. 

Then in my RLS filter I created the following dynamic

var user = if(selectedvalue(Viewer[Viewer]) = "All", "all@company.com",userprincipalname())
return
if( 
  countrows(filter(all(team),all = user))>0, all = user,
if(
  countrows(filter(all(team),Regional_Manager = user))>0, Regional_Manager = user,
if(
  countrows(filter(all(team),Team_Manager = user))>0, Team_Manager = user,
  Team_Member = user)))

They dynamic filtering in RLS is working based on wether the person is a team member, manager or regional manager, but it doesn't seem to be affected at all by the slicer to override the RLS.