Forum Discussion
Row-Level-Security Aggregation
With RLS you provide the access only to the selected rows. Make a table in Excel, filter it accordingly and see if you can calculate ALL things that you want from that filtered view. This will be your answer.
One workaround that comes to my mind is to calculate totals and # of customers in Power Query and store them as a separate table. Then, using RLS you can filter 2 tables in different way: the one that cotains all individual sales will contain only sales of that person, so he/she doesn't see other records. In the same time the same user will have an access to a separate table, calculated in Power Query that contains only aggregated numbers. Because calculation happens in Power Query, which is executed before RLS applied, the user will have the access to these aggregated numbers. Give it atry and let us know if you managed to obtain what you wanted!
P.S. a strong advice: use multiple table approach rather looking for wokrarounds with a single table on which RLS is applied 😉
Super UserHi matskah, as of my knowledge you can't obtain the requested result using Row-Level Security (RLS).
RLS will limit the number of rows that a "current user" has access to; therefore, his/her totals will be affected as well. If your table contains 100 rows, by applying RLS the version that a specific user see will only contain 10. He/she won't even know about the existance of the remaining 90 rows.
Depending on the importance of RLS, you might still obtain a similar user experience not securying the underlying data. The heart of RLS is USERPRINCIPALNAME() function - USERPRINCIPALNAME function (DAX) - DAX | Microsoft Learn By knowing the "current" user you can shape your report in appropriate way and you're not obliged to implement RLS. You can create measures that will calculate different results for each user, BUT, even if they see different results, tehcnically they will still have the access to the whole data (which will allow to see totals). Consider that if users get access to the dataset they will be able to see ALL data, so you need carefully evaluate if such risk is acceptable to guarantee the desired result.
Good luck with your project 🙂