Row-Level-Security Aggregation

[matskah]

Hello everyone,
I am still relatively new to this environment, but I have a very specific requirement in the context of row-level security:

The requirement comes from a customer in the pharmaceutical industry.
The following sample raw table is given:

In it we see the RepKeys and their associated Sales.

The goal should be that each Rep sees his/her own sales, the total sales of his/her Line, and the total sales of his/her Country, as well as the number of customers on each level.

Two examples:

When R11, opens the dashboard, (s)he should be able to see the following:

R11: Sales 100, 1 Customer

L1: Sales 600, 2 Customers

DE: Sales 1000, 3 Customers

When R21, opens the dashboard, (s)he should be able to see the following:

R21: Sales 400, 1 Customer

L2: Sales 400, 1 Customer

DE: Sales 1000, 3 Customers

How would I solve such a special requirement in PowerBI efficiently?
Can you please help me?

Thank you and best regards

[matskah]

 Hello @Sergii24 ,
that approach worked very well ! Thank you 🙂

[Sergii24]

Great! Good luck with your work 🙂

[v-ssriganesh]

Hi @matskah,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @Sergii24 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[matskah]

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?

[Sergii24 · Accepted solution]

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 😉  

[Sergii24]

Hi @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 🙂