Benchmark with security roles

[whereismydata]

Hi,

hope someone can help me with this:

My challenge is to create a benchmark dashboard with RLS in place.

The Dashboard is pretty simple. I'm using a scatter chart with revenue on x and 'share in percent' as values.

When RLS is applied, the users just sees his own datapoints.

I would like to achieve that users of a certain role can see all datapoints, but when he hoovers over a datapoint the tooltips just shows information about his own datapoint, eg, salesperson, client etc. When hoovering over another datapoint which is not assigned to his role, no information or reduced information (just numbers) should be displayed.

I thought of duplicating the tables and creating to identical overlapping visuals. Where, the one shows only the values of the role and the other all values. But this is complicated since the scale of the axis should stay the same, so I need to set the value. Also filtering is not really possible since I also need to duplicate the filters.

As soon as I create a relationship between the tables (1:1) RLS filters all tables (which is normally a good behaviour 😄 ). I did not find a DAX formula to bypass the RLS, and as far as I know, this is not supported at all.

Of course another solution would be to create several dashboards, but I do not want to go down this road...

Maybe this is an approach:
If it is possible to extract the assigend RLS-Role in a DAX then it maybe is possible to create a new column where all the sensitive information is anonymized exept his own?

Thank you so much in advance

Best

[lbendlin]

Early disclaimer: Security by Obscurity Does Not Work.

With that out of the way - one approach is to create a hidden shadow table with anonymized data that is not covered by RLS. I guess you can see where this is going.

Possible? Yes. Recommended? No.

[whereismydata]

Well, if I understood you correctly that is what I also thought of. Duplicating the table and hiding all information and no relationship between those two tables.

But the problem is, that I also do have to duplicate all filters and need to set the scale to a fixed value.

Since users only got read access on this report I thought maybe if I can somehow extract the applied role I can write a DAX which masks all other info.

If there is no DAX to extract the role, I thought of getting the role membership from AD and then extract the username with DAX.

Another solution I thought of is creating a view where i combine several tables and each table suits the security requirements of each users.

[whereismydata · Accepted solution]

For everyone reading this in future. I ended up creating separated views that suits the requirements for each users, adding a security column and combing all tables. Therefore, of course, without any role applied the data is duplicated.

Another benefit of this is, that you are much more flexible with this approach. Like, if one user does not want some datapoint to be shared with the others (because it would be obvious to guess who is behind the datapoint), it then can be displayed only for him, but not for the others.

If anyone is interested, I can share more details on my approach.

best