Dynamic RLS, controlled from SharePoint list, with ALL option

[Anonymous]

I have a Power BI report that includes (among many other things) a fact table and a Team dimension (containing columns for Team ID & Team Name).

I have configured Dynamic Row Level Security on this by creating a SharePoint list which contains a list of users by their Username, a Role ID & Role Title column (whereby the Role ID column matches the Team ID column from the Team dimension).

This all generally works fine when I bring my SharePoint list into my data model and create a relationship with the Team dimension (Team Dim 1<> * SharePoint list... that's a bi directional relationship).

The role has been set up as follows:

On the Team Dimension:

[TeamID] IN SELECTCOLUMNS(
FILTER(
'SharePoint List',
'SharePoint List'[Username]=USERPRINCIPALNAME()
),
"Role"
,[Role ID]
)

... and on the SharePoint list:

[Username] = USERNAME()

I now require to create a new entry in my SharePoint list with a Role TItle of "Master" (preferably with a blank Role ID) whereby anybody assigned this role will see everything. I've tried to put some sort of IF statement into my role (if Role Title = "Master", TRUE(), else "do the rest of the logic") but haven't had any success.

I'm considering having a 2nd SharePoint list for the "master" users, but hoped to do this neatly in one role/one list.

The idea is the customer managing this only need add users to a SharePoint list (and an AD group which handles them being part of the role and the published app) when granting them access - I don't mind telling the customer to use SharePoint list 1 for team-based access and the Master list for all access, but I don't want them to have to go into the Power Bi Service and add users to roles there too.

[Anonymous · Accepted solution]

Hi @Anonymous 

I think now your sharepoint list may be like my sample as below.

Due to you need to relate Role ID with Team ID for dynamic RLS .

Blank in Role ID will return to blank, so it is incorret.

If you only want to add master under your exist users in Sharepoint List.

You can try to update your Sharepoint List as below.

View as "User1@xxx.com",   I can only see Team A.

View as "User11@xxx.com", I can see all Teams.

My Dax is same as yours, it works well.

Or  you can try to build Dynamic Row Level Security with Organizational Hierarchy in Power BI.

You can refer to this blog for more details : Dynamic Row Level Security with Organizational Hierarchy.

Video: Row Level Security Organizational Hierarchy

Best Regards,

Rico Zhou

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

[ToddChitt]

Sounds like you should have two Roles in Power BI, and two AD Security Groups. One Role/AD Group is for those people that should be governed by RLS, and one Role/AD Group for Master. In Power BI, simply create the Role, but don't but any Security filter statements on it. 

Once it is deployed, on the Dataset Permissions tab, add the AD Group for Master to the Master Role. 

Now, anybody that gets added to the Master AD group will have unfiltered access to Power BI data.