Forum Discussion

Aroth's avatar
Aroth
Advocate II
9 years ago

RLS need some advice

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

Need Help
Tips and Tricks

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Aroth
      Advocate II
      9 years ago

      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 !!