RLS need some advice

[Aroth]

Hi everyone, 

I just get the Pro licence of PBI and I have some questions about the RLS.
I'm creating a report that will be shared to a large group of people. Everyone will have custom access to the report according to their Role in the company. That mean that I will have to create lots of different RLS (could be around 30 ) .
Could this harm the Report efficiency ? 

I've noticed that the RLS is not very flexible. I tried to restrain a user to a role I created in order to test it. Then I removed the user from this role on the security list so he could see the full report again but he is now unalble to see the report.
I tried to create a Role with no filter that would be the "global role" but it didn't work. 
Has someone an idea how I should do it ? 

If you have some advice or helpful tips please don't hesitate to share them.

Thanks,
Aroth

[v-sihou-msft]

@Aroth

In this scenario, if you want to configure Row Level Security based on incoming users' role in company. You don't have to create one RLS for each role. I assume you should have a UserInfo table which contains the role information of each person. You just need to use LOOKUPVALUE() to get the [Role] column based on USERNAME() in row level filter.

[Role]=LOOKUPVALUE(User[Role],User[Name],USERNAME())

When you share a dashboard with RLS applied, the end users must be within a role. Otherwise they can't access the dashboard. Or you can also grant the Group admin to users, then the RLS will not apply on them.

As I tested, if I create a role with no filter, the member of this role can view all data.

Regards,

[Aroth]

Hi @v-sihou-msft, thanks for the advise

As I'm trying to follow your recommandation I have few more questions...

Just to explain the context:  the users are sales leaders that have a team with "sales person". So I want each sales leader to see their team. It means that the "Role" of the users are their Name because I created an other mapping table with the Name of the leader > each member of the team >  account ownership;

For Example , I have those three tab with the relationship :

So I tried two RLS formula :

[Role]=LOOKUPVALUE(ROLE[Role],ROLE[User],USERNAME())

[Role]=LOOKUPVALUE(ROLE[Role],'ACCOUNT OWNERSHIP'[Sales Leader],USERNAME())

But they didn't works...

It would be great if you know where the problem is.....

Thanks a lot !!