Dynamic Row level security

[Krish89]

Hi,

I have a requirement like below:

I have two tables "SalesRep" and "Sales" as below.

I need to implement RLS on these tables. If SalesRep "Daniel" logs in to Power BI service, he should see his sales data i.e., row 1&6 in the Sales table.

If SalesRep "Leo" logs in to Power BI service, he should see his sales data i.e., row 5&7 in the Sales table.

But if manger logs in, he should see all the sales reps data under him.

For Eg: Anderson should see the data of Daniel, Philip and Sonia and Jakub should see Sonia and Leo's data.

           

Let me know if you have any thoughts.

Thank you,

Krishna.

[Icey]

Hi @Krish89 ,

You can create two roles by clicking the Manage Roles under Modeling: the Manager role and the Sales Rep role. And you need to make sure the relationship between your two tables is correct.

IF(MAXX(FILTER('SalesRep','SalesRep'[Manager Email]=username()),'SalesRep'[Manager Email])=username(),'SalesRep'[Manager Email]=username())

IF(MAXX(FILTER('SalesRep','SalesRep'[SalesRep Email]=username()),'SalesRep'[SalesRep Email])=username(),'SalesRep'[SalesRep Email]=username())

After the roles are created, you can enter the email to test as below:

You can check more details from here.

Best Regards,

Icey

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

[Krish89]

Hi @Icey ,

Thank you very much for your help and the solution is perfectly working🙂

I would like to understand that the roles(salesrep and manager) are applied on the table(SalesRep) or on the columns inside the SalesRep table.

Also Could you please brief me on the DAX logic you have written?

Thank you!!

Regards,

Krishna.

[Icey · Accepted solution]

Hi @Krish89 ,

About the Manager role, it first filters out the e-mails of the managers in the ‘Sales Rep’ [Manager Email] and uses the username() function to determine if the current user is inside. If he is, he will see the information that belongs to him.

For example, Anderson is the current user, and his e-mail is in the [Manager Email] column, then he’ll see the information circled by the red box and other table information associated with this table after filtering.

About the Sale Rep role, it first filters out the e-mails of the Sales Rep in the ‘Sales Rep’[SalesRep Email] and uses the username() to determine if the current user is inside. If he is, he will see the information that belongs to him.

For example, Daniel is the current user, and his is in the Sale Rep role, then he’ll see the information circled by the red box and other table information associated with this table after filtering.

Reference:

Dynamic Row Level Security with Manager Level Access in Power BI

Dynamic Row Level Security with Power BI Made Simple

Best Regards,

Icey

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

[Krish89]

Hi @Icey,

The below solution works well in Power BI desktop but when I publish it to the service, the roles are not working.

When i test the roles, the data is not filtering and ended up with error. Is it working for you in the Power BI service?

Thank you!!

Regards,

Krishna.

[Icey]

Hi @Krish89 ,

I reproduce your issue when I didn't add the users to the corresponding roles.

Only members that are added to the role can see the specified information. RLS is valid if the member is added to the role and does not have editing rights. Please refer to this official document to add users to corresponding roles.

I replace anderson@xyz.com and daniel@xyz.com with my test emails and add the test emails into Manager role and Sale Rep role.

The user(Anderson) in Manager role opens the pbix file as shown below.

The user(Daniel) in Sales Rep role opens the pbix file as shown below.

Best regards

Icey

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

[Krish89]

Hi @Icey,

Thank you very much for the explanation and it makes me clear and understandable.

Regards,

Krishna.

[amitchandak]

@Krish89 , refer if these can help

RLS - Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/
https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi