Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Noogin
Frequent 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
Frequent 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors