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 😉
Hello Sergii24 ,
thanks for your fast reply. 🙂
Okay I got your point, but there sure must be some way to be able to aggregate the results and make sure that each user is only allowed to see his/her respective rows, right?
I thought about recreating the given information with other levels (see new information in red).
If I would allow "R11" to see information associated with RepKeys R11, L1 and D, I would be able to obtain the desired results, or am I missing something here?
Big drawdown would be the multiplication of rows and therefore performance impact...
Is there surely no other way?
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 😉