RLS for "Reporting Groups"

[Friendly]

I want to implement RLS so users can see the financial data of other users in their "Reporting Group".

A Reporting Group is just a group of users that should be able to see each other's data.

For example:

• User 1 and 2 are in reporting group 1, they see each other's data.
• User 3 and 4 are in reporting group 2, they see each other's data.
• User 1 and 5 are in reporting group 3, they see each other's data. (I know that in the data there isn't a user 5 this is an example of how I would like it to behave)
• etc etc

Any user can be in any number of reporting groups, and the reporting groups have no limit to the number of users.

What I have is a prototype set up of the data. It looks like this:
4 tables:

1. Client

2. Account

3. ReportingGroup

4. ClientReportingGroupMap

They are connected like this:

The expected behaviour is that if Mickey (ClientIF 101) logs in he should be able to see the accounts data for people in Reporting Group 1 or 3. In Reporting Group 1 there is Mickey and Minnie. In reporting group 3, it's just Mickey.

Then if Daffy or Daisy log in, they should both be able to see each other's data as well.

Can someone help me take a stab at the dax for the RLS? Everything I come up with only shows individual client data, and not the data for the other people in the same reporting group.

Or, let me know if I'm going about this wrong?

Cheers!

[TomMartens]

Perfect! Happy to help!

[TomMartens · Accepted solution]

Hey @Friendly 

here you will find a pbix

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/ERItNG-FA8pImPWOA8MaNJ4BuRv52...

that contains a solution based on adapting the semantic model: 

I tend to adapt the model instead of leveraging complex DAX statements. The reason for this is that RLS means table iterators are in place, no matter the DAX statement that "defines" the RLS. From my experience, the more simple the DAX, the lesser the performance impact of RLS on the overall query performance.

For this reason, I created a new table, "Security Table" using Power Query. Maybe there are different ways to create this table, I created this table based on the sample data. This table is based on two joins, starting with the Client table. I create a new table by joining the Client table with the ClientReportingGroupMay table. This table provides all the ReportingGroups to which a Client is assigned. The result is shown in the next image:

Then, I joined the "ClientReportingGroupMap" table a second time, but this time, I used the ReportGroupID column, which provides the ClientIDs within the reporting group. This table contains the "peers"; in my example, I only used the ClientID column when expanding the table. I did not rename the column ClientID.1 to "Peers." I think this helps to better understand my approach. The next screenshot shows the final "Security Table" table:

The next image shows the relationship between the "Security Table" and the Client table:

The next image shows the configuration of the Row Level Security:

And finally, a very simple report, testing the RLS as user "client101@example.com:"

Hopefully, this provides what you are looking for.

Regards,

Tom

[Friendly]

You're a legend Tom! This absolutely fits the bill. Thank you so much.

[TomMartens]

Hey @Friendly ,

upload the pbix to OneDrive, Google Drive, or Dropbox and share the link.
Make sure that the column email contains email address contains email-address (even if they are not valid), as most likely the DAX for the RLS will leverage the DAX function USERPRINCIPALNAME.

Regards,

Tom

Regards,

Tom

[Friendly]

Hi Tom - I DM'd you the files. Cheers!

[TomMartens]

I will not work with files shared via DM!

Regards,

Tom

[Friendly]

Hi Tom,

Here is the Excel file and here is the PBIX file.

Cheers.