Enable/Disable RLS by report

[AUaero]

Hi,
Here's the background:  We've implemented a split backend/frontend model in the PBI service where data models live in one workspace and the live connection reports to those data models live in various other workspaces.  We've also implement RLS on our sales model to control access to data at the sales person, sales manager, and general manager roles.
However, we've got some reporting that a person at the lowest security role (a salesperson) needs to see data for the entire company.  For example, we publish daily rankings of salesperson performance where each salesperson is ranked against all other salespeople.  If RLS is enabled for this report, the salesperson will only be ranked against himself since RLS will filter out data for all other salespeople.

Our solution to this problem has been to create two separate data models - one that has RLS and one that does not.  Reports that need to be controlled by salesperson are built from the RLS model while others such as the salesperson ranking are built from the non-RLS model.

This works but isn't ideal.  We have to maintain two versions of the data model and any changes have to be implemented twice to keep both models up to date.

Is there a way to create a data model that can toggle RLS on/off for a specific report?  I tried creating something in the model using a disconnected table and slicer to capture "RLS on" or "RLS off", but I can't see any way to use SELECTEDVALUE in the context of a RLS dax filter on the user table.

Any thoughts or suggestions would be most welcome.

Thanks!

[the_machine_11]

I'm stuck with this as well, can't get the RLS working in 1 report but not the other. Could you share more details on the disconected table solution?

[v-xiaotang]

Hi @AUaero 

Have you solved this problem? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.
really appreciate!
Any question, please let me know. Looking forward to receiving your reply.

Best Regards,
Community Support Team _Tang

[v-xiaotang]

Hi @AUaero 

what's your datasource? personally speaking, if you can depoly on-premise datasource, then create 2 reports with the datasource, after the reports uploaded to Service, one report's dataset set RLS, and another one for salesperson is non-RLS. then you only need to maintain the on-premise datasource.

Best Regards,

Community Support Team _Tang

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

[parry2k]

@AUaero Seems like you are on the right track if you have disconnected table and with the selection, you can force the RLS rules, not sure why it wouldn't work.

Can you share what DAX expression you are using for RLS?

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[AUaero]

Thanks @Greg_Deckler - I had this thought as well and for some things (like my ranking example) I think it could work fine.  Still, it would be nice to just turn RLS on/off so that we retain the maximum flexibility when designing the reports.

[Greg_Deckler]

@AUaero You'd probably have to submit this as an Idea if it is not already there. Actually, did you just do that? Microsoft Idea  · Enable/Disable RLS at the Report Level for Reports With Shared Data Models (powerb...

I voted for it! Not sure if it is exactly possible and seems like a potential big security risk actually so not sure it would ever be implemented.

[AUaero]

LOL @Greg_Deckler thanks for voting.

I agree, it *could* be a security risk, but I wish enterprise software would trust users a little more.  As in, throw a great big warning box on there that says, "hey dummy, if you turn this on we aren't responsible if you share data that you shouldn't have."  I can decide whether I want the risk or not.  😁

[Greg_Deckler]

@AUaero Typically the way I have seen this handled is to create an "aggregation table" that summarizes the information in the report. This is done as a DAX calculated table and no RLS is applied to it. Then you have a single data model.