Forum Discussion
RLS for SSAS on Prem not Working
Hi, niklanzo91
I created your tables and used them in SSMS, the data in RLStable is a test mailbox:
In Power BI Desktop, tap the Modeling tab, and then select Manage roles. Create a new role, such as BranchRole. Add the following DAX formula to your BranchTable:
[CompanyID] = LOOKUPVALUE(RLSTable[CompanyID], RLSTable[UserEmail], USERPRINCIPALNAME())Publish to the Power BI service to configure RLS in the Power BI service. In the Power BI service, open the published report and tap Settings. In the Security tab, select the role that you created, BranchRole. Add the users or groups you want to test.
The creator's perspective is to be able to see all the data.
I have another user in my workspace. Until I added it as an RLS role, he couldn't see the data for my reports.
When I add it to the RLS role, he will only see the data that corresponds to his email address
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi NandanHedge,
thanks for your reply.
I can confirm that the user email is the same of the result of USERPRINCIPALNAME (I tested it with a colleague).
Where can I test them within SSAS? With MDX Queries?
I leave here an example of the structure. I don't filter directly the fact table, but a dimension table related with the fact table.
This is the dimension table (branch_table):
| CompanyId | Description |
| 01 | UK |
| 02 | US |
| 03 | DE |
and this is the RLS_table. One user can see more than one branch:
| CompanyId | User |
| 01 | [email protected] |
| 02 | [email protected] |
| 03 | [email protected] |
Hi, niklanzo91
I created your tables and used them in SSMS, the data in RLStable is a test mailbox:
In Power BI Desktop, tap the Modeling tab, and then select Manage roles. Create a new role, such as BranchRole. Add the following DAX formula to your BranchTable:
[CompanyID] = LOOKUPVALUE(RLSTable[CompanyID], RLSTable[UserEmail], USERPRINCIPALNAME())
Publish to the Power BI service to configure RLS in the Power BI service. In the Power BI service, open the published report and tap Settings. In the Security tab, select the role that you created, BranchRole. Add the users or groups you want to test.
The creator's perspective is to be able to see all the data.
I have another user in my workspace. Until I added it as an RLS role, he couldn't see the data for my reports.
When I add it to the RLS role, he will only see the data that corresponds to his email address
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.