Using DAX instead of RLS for multiple reports/security roles?

[EduSurveys]

I have a report with three different pages:

• Page 1 = University view - I want all employees to be able to see all the student grade data in aggregate
• Page 2 = Department head view - as the "manager," I want the department head to be able to see aggregate data about their own courses and data about courses taught in their department (i.e., data of other instructors)
• Page 3 = Instructor view - I want instructors to be able to see grade data from students in their own courses. Department heads will also be able to see data for courses in their departments.

Ideally, I'd like to be working from the same dataset, but if I set up row-level security for instructors and DHs, then they will only be able to see their own data on the company view page, as explained in this article https://radacad.com/share-different-visual-pages-with-different-security-groups-in-power-bi. So that would mean I have to have 3 separate reports and at least 2 different datasets if I want to set up RLS. I currently have RLS set up on DimCourse, which is a dimension table that connects course to my FactGrades table. 

Is there a way to use page filters and a DAX expression to do this instead of RLS? I'm pretty sure that you can't use DAX expressions as filters, so I'm trying to think of other creative solutions but drawing a blank. It would be an unconventional way of using filters, but I'm wondering if something like this has been done before to allow the use of a single dataset. Thanks!

1. Proposed DAX expression, instead of putting RLS on DimCourse: Instructor_UserPrincName = IF(CONTAINS(DimCourse, DimCourse[COURSE_PROF_EMAIL], username()), "Y", "")
2. Then, create a "Filter on this page" on Report Page 3 to be Instructor_UserPrincName = "Y"

[harshnathani]

Hi @EduSurveys ,

Check the below links

https://radacad.com/secure-the-sensitive-data-in-power-bi-data-masking-better-with-row-level-securit...

https://radacad.com/show-the-information-but-not-the-details-power-bi-data-masking

Regards,

HN

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[EduSurveys]

@harshnathani , thanks for your proposed solution. I should clarify that I do not intend to give end-users access to the underlying dataset to make new reports. I just want end users to be able to see different data in the cloud service depending on which page of the report they're viewing. As a developer, I want to avoid having to make a different dataset for each report. However, some pages in the report require RLS (Instructor view and Department Head view), while some need to show all data (University view).

[lbendlin]

This depends very much on the sensitivity of your data but here is my approach:

I had a report for a group of sales reps that was controlled by RLS.  It worked as designed, but it was totally not what the reps wanted.  Yes, they need to focus on their area, but they also need to occasionally compare across regions etc.

The solution I came up with is to replace the "restrictive" paradigm of RLS with a "focused"  paradigm that allows the sales reps to set a global filter to their name so they can focus on their area, but also allows them to clear the filter and see everything.

I fully understand that this is just one of many different business scenarios, and may or may not be applicable to your situation. But my sales reps are very happy with losing the shackles of RLS. 

Net-Net - only use RLS if you absolutely, positively must do so. If possible, rely on non-technical agreements ("we're all adults here", or legal agreements) for keeping your data safe.