cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Noogin
Regular Visitor

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

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

1 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Noogin
Regular Visitor

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

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Kudoed Authors