Reporting overall totals along with user specific data using row level security (RLS)

[Noogin]

Greetings everyone,

My office and I are new to Power BI and come from a SAS Visual Analytics environment. We are trying, most likely without spending the needed time watching videos and training materials, but our transition window is short. We have a good grasp on creating reports but have a challenging requirement. We are only looking for direction on where to find and answer if one is available. We will certainly do the necessary work to get the report up and running, we just want to start at the right place. 

Here is a simplified explanation of our challenge; we are in higher education and need to provide an overall success rate for all courses in each department as well as for a specific course which we can do. In addition, we also need to display, in the same report, a specific faculty members success rate for the same course. The wrinkle is this is that it needs to be done with row level security because the college has a policy of not allowing faculty members to view other faculty members information. In other words, when a faculty member views the report, they should see the following:

• The success rate for each course(s) they taught. That is, the faculty member’s specific course data.
• The overall aggregated department success rate that offers the courses.
• The overall aggregated success rate for the specific course or courses the faculty member taught. In other words, the ability for the faculty member to compare their student success rate to the aggregated success rate of all other faculty who taught the same course.

We were able to do this in SAS Visual Analytics using parameters and two separate data sources. It was clunky, but it worked. I do not see anything that looks familiar in Power BI, but I admit, we are still at the base of a steep learning curve and timetable.

Any guidance is greatly appreciated.

Sincerely,

Gary

[Noogin]

Thank you @Greg_Deckler !

Just so I understand, you  do this using the Power Query Editor or with a DAX calculated table and not import an additional table with aggregated values?

Gary

[Greg_Deckler · Accepted solution]

@Noogin You could do that as well if you have a pre-aggregated table in your source. Otherwise, in Power Query reference your original query and then do a Group By step to aggregate the values. Or, just have one query and use a DAX calculated table using something like GROUPBY or SUMMARIZE to get the aggregates.

[Greg_Deckler]

@Noogin Normally what you do in these types of situations is create an aggregation table. You can do this in Power Query Editor or with a DAX calculated table. This provides the aggregate values and is not subject to RLS. Then you have your main table that is affected by RLS for the individual reporting.