RLS for "Reporting Groups"

Hey Friendly 

here you will find a pbix

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/ERItNG-FA8pImPWOA8MaNJ4BuRv52JR4vNciIcIRmOs6iQ?e=DT8VMJ

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 "[email protected]:"

Hopefully, this provides what you are looking for.

Regards,

Tom