RLS Problem

[azaterol]

Hello everyone,

I have the following problem.

Here is the sample to download:

https://easyupload.io/bzdxi0

I would like to activate the RLS in my report and would like to work according to the classic hyrarchy system. The agent boss can see all the agents' data and the agent himself can only see his data.

For this purpose, I created a table = "Views" with ID_No and name.

This "view table" points to agents.

Now the agents can see all of the other agents' data on one page of the report. So in principle they only take on the role of agency boss on this page of the report.

Should be displayed this:

On all other pages (here "Big Sales") , the agents fall back into their classic roles and can only see their data.

Should displayed this:

How can I display this here?

I'm stuck and I need your advice. Thanks.

[Anonymous]

Hi @azaterol,

I think you may need a user table to mapping username and correspond user roles. Then you can use username/ userprincipalname function to find out the role, then you can use this as condition to ensure which level the RLS filter apply to achieve dynamic filter effect on different hierarchy levels.

USERNAME() v/s USERPRINCIPALNAME() in RLS for Pow... - Microsoft Fabric Community

Regards,

Xiaoxin Sheng

[azaterol]

Hello @Anonymous ,

Unfortunately that didn't help me. I have uploaded an example, would it be possible to use the example to illustrate what you mean?

[Anonymous · Accepted solution]

HI @azaterol,

I mean you need a user table to mapping 'user id', username and user roles. You can use the dax function(USERNAME, USERPRINCIPALNAME) that I mentioned above to get the current user role and user id.

Using Username() in DAX with Row-Level Security | Microsoft Power BI Blog | Microsoft Power BI
Then you can use this role as condition to write a if statement expression to redirect to different filter expressions.
For example:
Current user is manager, you should filter on user id field or manager field which match with current user.
Current user is employee, you should filter on the 'user' id field.

Regards,

Xiaoxin Sheng