PBI - Row Level Security for different user types

[bcap01]

Hello,  

I was able to create RLS for users so that the user logged on can only see their data when on a report.  I did this by using the following code snippet:

[email] = userprincipalname()

However, depending on the user, I would like to make it so the USer can see ALL data.  How is this possible?

For example:

IF Email ="johnDoe@test,com" or "JaneDoe@test.com'

THEN "See all users data"

ELSE

"See my data only"

END

[Mikelytics]

Hi @bcap01 

based on your description the approach I provided you should work. But to help you more I need the following indformation:

- date model picture with relevant tables and columns

- roles created incl. the rules per role

- people assigned to the rrole (can also be person a, person b person c etc.)

- expected behaviour vs. actual behaviour

Best regards

Michael

Best regards

Michael

-----------------------------------------------------

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

Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Mikelytics]

Hi @bcap01,

From my understanding you created a role (lets call it role A). In role A you already defined the rule

[email] = userprincipalname()

and you also already assigned people to this role in the Power BI service so that they can only see the data which they are allowed to, correct?

Now you want additionally to give specific people the ability to see all data independent from their e-mail address. You can achive this by the following steps:

1. Please create a second role (we call it role B)  in Power BI Desktop

2. in role B please do not put in any rules

3. Now again publish your report to the service

4. now assign all people to role B which shall see all data.

Result: Now all people assigned to role be should see all data.

Best regards

Michael

-----------------------------------------------------

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

Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[bcap01]

Hi,  For some reason my Roles seem to work in PowerBI Desktop, but when  I Publish to Power BI Service, I assign users to the Two Roles, but in each case all data is being returned. 

For example, "Leadership" is intended to see ALL data.  "Staff" is intended to see their data only.  "Leadership" and "Staff" for some reason both see all data.  I have assigned folks to each group via PowerBI Service.

Is there anything at the Power BI Service level or Desktop that I may need to configure?

[AnastasiaS · Accepted solution]

Hello @bcap01 

You can define user access in Power BI service.

Let's suppose that you defined 2 roles in Power BI desktop:

Director : global user who sees everything; you can use the "true()" function in the "Dax expression" tab

Salesperson: users who only see their data; for this one you have [email] = userprincipalname()

Publish the report and then, go to the "..." option of your dataset ->  Security

and then specify the e-mail adresses or security groups for each role:

once the persons added, you can click on "..." near the role and "Test as role":

You'll be able to test as a specific person by providing its e-mail.

Can you try to do that?

Don't hesitate if smth's not clear.

Regards,

[bcap01]

Many thanks!  I have taken these steps, and what is strange is that it seems to generally work in Desktop, but then when I publish, the "Staff" level will show ALL data for the Staff member instead of just the data that applies to that stuff member.  "Director" level shows all data as expected.

[AnastasiaS]

Hi @bcap01 

Can you try to test directly as "staff role"? without providing a person e-mail.

This is to check if there is the difference between the staff & director roles in terms of data.

Per info, there are cases when the RLS won't work. For example, when the specified person is an Admin, Member or Contributor of the workspace. Can you check the workspace access and identify the tested "staff member" permission?

Regards,

[bcap01]

Actually, I am finding that the RLS Secuirty works when I do the "Test as ..."  in Power BI Service.  However, when I have users test they are able to see everything as if they are a :Director" even if I have set them to "Staff".  Is there another place I need to configure this to make Security work?

[bcap01]

Hi, 

Using my email which is ADMIN:

Staff Role - No data results return

Director Role -  All data results return

Viewing as another user ... when attempting to View the Staff role as another user the data results come up as "BLANK".  The expectation is that the data results would show for the specied user only. 

Are you saying this will not work becuase I am logged in as myself? Is there a way I can View another user in BI Service like I can in the PBI Desktop version?

[AnastasiaS]

Hey @bcap01 

Normally if the RLS was set up correctly the steps you mention should work.

It is difficult to say what is missing without having access to the data & tool.

Let's try to gather all infos to make a last check.

Could you please, if possible, attach the following screenshots (of course by replacing real names with fictive data; just please keep consistency when replacing them to have a clear idea of how, for example, user A is set).

1. RLS in Power BI desktop where we have the 2 roles + the dax definitions for both

2. Security set in Power BI service. With the 2 roles and the memberships (by replacing real names with fictive data, as long as we have same examples everywhere).

3. Workspace membership. Do the users added to the RLS have access to the Workspace, and which access do they have? admin, contributor, viewer?

This can be viewed by going to the access option within the workspace : 

4. Are we sure that users use the right accounts to connect? could there be a difference between the "test as" account and the person signing in? (eg external accounts..)

Could you also precise what is the datasource for your report? database, files, cubes...

Thanks in advance and hope that we'll find the mistery behind this issue!

Regards,

[bcap01]

@AnastasiaS I think I figured out the problem.  I had my report in "Public Channels", I created a new directory and added my project to it and seems to be working.  Many thanks for your help!