Power BI Report Security - Tenant solution

[Jon von der Heyden]

Hi All

I'm looking for a pointer here because I haven't found what I am looking for using the MS Docs website (although no doubt it is there).

I have single table of data for all of our customers. What I want is to build a single dashboard of various aggregations of this table. They key point to note is that the underlying table contains data for all customers.

What I want is that a customer is to log in, they should only see their own data, not the visualisations and underlying data of other customers.

Given that I have hundreds of customers, I want to avoid replicating for each customer.

Can you direct me so that I read the approprtiate guidelines and instructions on how to set this up?

 

[Jon von der Heyden]

Update: It helps if you know what to search for. What I was after is called "Row Level Security" or RLS for short.

Turned out to be really easy. I see the question has been asked a few times on this forum, but none that I have seen have any replies or complete solutions. So, if you have come across this thread looking for an answer, here is some guidance:

Microsoft documentation - Row-level Security

Key steps:

• I have a table of users (their email addresses) mapped to their respective customers (since I am restricting customers to their own data).
• Create relationships between my users table and my data tables, on customer ID (I have many to many, because I have users with access to multiple customers - i.e. agents).
• Create a new role. Mine is called "Customer". I have done via PBI desktop Modelling > Manage Roles. It is set-up on my users table, with following DAX: [User] = userprincipalname()
• Note - key for me was to use userprinciplename, and not username, because it is matching on customer email addresses.
• You can test the restrictions by going Modelling > View As. From their you can emulate access of a specific customer.
• If it works, publish it to a workspace. Key is that the users are NOT set-up as members with edit on the workspace. If you do the RLS won't work.
• In the workspace, click the burger by the Dataset (not Report) > Security. In there you set-up your users. In this case I added each customer email address one at a time.

This worked for me. Now my customers can access a dashboard and the data is filtered for their data only.

Good luck!