Row Level Security: Exclude users from seeing data who are not in a particular AD security group.

[BarnyQuack]

Hi,

I want to prevent users not in an Active Directory group (called 'Payroll Users')  from seeing data  when Department = "Payroll"

There will be a small number of users who will be able to see all data, but the vast majority will not be able to see it.

Department	Invoice
Payroll	123
Payroll	456
London	111
Edinburgh	222
Glasgow	444
Payroll	789

I've made a start, but need a bit more help please!

So far I have:

For security roles I have a role 'Payroll Hidden'  where [Department] <> "Payroll"  : this is for users not in the AD group.

there is another role called ALL that can see all data, i.e. no data is filtered, and that is for users in the AD group.

Many thanks for looking

[amitchandak · Accepted solution]

@BarnyQuack , Seem like roles are correct. Ensure that you assign security groups to these roles in Power BI Service. Under Sematic model Security option 

[v-dineshya · Accepted solution]

Hi @BarnyQuack ,

Thank you for reaching out to the Microsoft Community Forum.

You are expecting that, Users in 'Payroll Users' AD group can See all data, including Payroll. And Users NOT in 'Payroll Users' AD group can see everything except Payroll.

Please create Roles.

1. Role--> ALL

DAX Filter --> No filter or [Department] = [Department] to allow all data.
Assigned to --> Members of the 'Payroll Users' AD group.

2. Role --> Payroll Hidden

DAX Filter --> [Department] <> "Payroll"
Assigned to --> All other users.

Please follow below steps.

1. Create Roles in Power BI Desktop, Go to Modeling --> Manage Roles. Create ALL --> No filter and Payroll Hidden --> Add filter as [Department] <> "Payroll". Then Publish to Power BI Service.

2. Assign Roles in Power BI Service, Go to Dataset --> Security. Assign ALL --> Add 'Payroll Users' AD group. And Payroll Hidden --> Add other users or groups.

Note: Use "View As Roles" in Power BI Desktop to simulate each role and confirm the filtering works as expected.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @BarnyQuack ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @BarnyQuack ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @BarnyQuack ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[BarnyQuack]

Hi Amit, many thanks for your quick reply! 🙂 

I'm hoping just to have a few people in the ALL AD group, where they can see Payroll data. But I'm hoping not to have to add hundreds of people not in Payroll to either an AD group or adding individually to a role. I was hoping there may be a better solution.

[pbiuseruk]

Hello,

You're pretty much there.

Make the 2 roles as you've mentioned and then go to "View As" to test that it's working properly.

Once you've done that, you need to check that your modelling is done correctly, so that the enforcement goes to any linked tables aswell. Check the "bidirectional cross filtering" option, when you're using RLS.

Then just publish the report when you're happy and go into Power BI service, into the Security part of the settings for the report and then add the users to the security roles. Then just share access with them and everything should work as expected.

The official docs are here: https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security.

Just assign the AD/Entra group to the security role in the service once you're done and it should all work as expected.

[amitchandak · Accepted solution]

@BarnyQuack , Seem like roles are correct. Ensure that you assign security groups to these roles in Power BI Service. Under Sematic model Security option