MANAGE ROLES AND SYNTAX IN POWER BI DESKTOP

[emma313823]

Hi All

Hitting a snag on syntax for row level security/managing roles in power bi desktop. First I created the role names in the software and published and now see them in the security area of the dataset online. In the software, I've selected one role and the table I want.

Here is what I want to do...I have a sales person (BAKER) in the table and he should only see the rows applicable to his name.

I know the dax should be

[salesrep] = "BAKER, DAVE"

There are 5 territories in the table and another sales rep called New Business Development is across all territories. I want Dave to see only the New Business Development data in the territory he is in which is NE.

for New Business Development to also be included, I did this.

[salesrep] = "BAKER, DAVE"||[salesrep] = "NEW BUSINESS DEVELOPMENT"

This seems to have worked and I now see only these two sales people in the data. Now I want to restrict the New business developement to the territory of NE only. Dave is also in NE. Can anyone help with the Syntax to help get this to work? I tried adding the territory of NE, but it did not seem to work even though when I checked the syntax it seem to accept it.

[salesrep] = "BAKER, DAVE"||[salesrep] = "NEW BUSINESS DEVELOPMENT"||[territory] = "NE"

Part 2 to this is that I have 2 sales people who are in two territories at once, so would need to understand how the syntax would be to restrict to that sales person, which I have below in the first part of the syntax, but need to understand how the syntax needs to be to only include the NEW BUSINESS DEVELOPMENT from Metro and PA territories.

[salesrep] = "VOORHEES, RUSS"||[salesrep] = "NEW BUSINESS DEVELOPMENT"||

Emma

[v-yiruan-msft]

Hi @emma313823 ,

You can update the DAX expressions as below:

[salesrep] =
"BAKER, DAVE"
    || ( [salesrep] = "NEW BUSINESS DEVELOPMENT"
    && [territory] = "NE" )​

[salesrep] = "VOORHEES, RUSS"
    || ( [salesrep] = "NEW BUSINESS DEVELOPMENT"
    && [territory] IN { "Metro ", "PA" } )

You can also refer the following links to get it.

Introduction to Row-Level Security in Power BI

Power BI Row-Level Security And Where To Filter

Best Regards

Rena

[emma313823 · Accepted solution]

Hi Rena I figured this out. Thanks so much for your assistance!

Emma

[v-yiruan-msft]

Hi @emma313823 ,

It's glad to hear that your problem has been resolved. Could you please mark your post as Answered? It will help the others find the solution easily if they face the similar problem with you. If it is convenient, could you please also share your solution for part 2? Thank you.

Best Regards

Rena

[emma313823]

Hi Rena

The first one worked perfectly, but the second one where I have a sales person in two territories did not. I tried typing what you had and it didn't work, copied and pasted what you had and that didnt' work, and I copied pasted and removed spaces, but that did not work either.

When I did the first one and select view role as...I saw that under the filter for Salesrep...it show only Baker, Dave and New business development and under Territory it showed only NE...so perfect.

When I did the second one...all 3 of what I mentioned above saved with no syntax error, but under the filter of Salesrep it still showed all of my sales people and all territories stil showed. Nothing seems to have restricted as in the first one with Baker.  Any thoughts on this to make it work?

Emma

[v-yiruan-msft]

Hi @emma313823 ,

Could you please provide some sample data of tables which include salesrep and territories and your desired result of part 2 in the form of screenshots? Please mask or exclude the sensitive data. Thank you.

Best Regards

Rena

[Sumanth_23]

hi @emma313823 - I would recommend it would be easier to setup seperate roles based on what each role would need to view in the reports and then assign the required users to the specific roles. 

Please follow the steps as per the Microsoft documentation to create and assign members to specific roles for RLS to work as expected.
https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

Also RLS is only applied to folks with "Viewer" access to reports / workspace; RLS will not apply to users with higher levels of access.

Let me know if I have understand your requirement correctly. 

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!