Forum Discussion
Row Level Security: Exclude users from seeing data who are not in a particular AD security group.
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
BarnyQuack , Seem like roles are correct. Ensure that you assign security groups to these roles in Power BI Service. Under Sematic model Security option
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
7 Replies
- amitchandak
Super User
BarnyQuack , Seem like roles are correct. Ensure that you assign security groups to these roles in Power BI Service. Under Sematic model Security option
pbiuserukResolver IV
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.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.
v-dineshyaCommunity Support
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