Forum Discussion

Lucy01's avatar
Lucy01
Icon for Advocate I rankAdvocate I
1 month ago
Solved

Row Level Security Errors

I have a report that is nearly ready to publish and I am trying to assign RLS so each team leader only sees their own team’s results. I’ve created an excel spreadsheet that records each team leader’...
Need Help
  • v-sathmakuri's avatar
    v-sathmakuri
    10 days ago

    Hi Lucy01​ ,

    Create a separate DimTeam table containing one unique row per team:
    DimTeam = DISTINCT('DataSheet'[Team])

    Then create a 1-to-many relationship:
    DimTeam[Team] (1) -> DataSheet[Team] (*)

    Keep your Team Access table as the mapping of UserUPN -> Team. Apply RLS using USERPRINCIPALNAME() to identify the user and return their team, then filter DimTeam.

    The flow should be:
    User -> Team Access -> Team -> DataSheet

    This avoids trying to use DataSheet[Team] directly in the RLS expression when it contains multiple rows.

    Thanks!!

Praful_Potphode's avatar
Praful_Potphode
Icon for Super User rankSuper User
1 month ago

Hi Lucy01 

While testing RLS, did you select your role and Other user as well?

When we are testing locally for any dynamic rls role, we need to select role and other user.

in the other user , enter emailid you want to test like below.

 

 

If this doesn't solve the issue, let me know.

Please give kudos or mark it as solution once confirmed.

 

Thanks and Regards,

Praful

 

  • Lucy01's avatar
    Lucy01
    Icon for Advocate I rankAdvocate I
    1 month ago

    Thank you, yes I was selecting role, other user and entering the email address as you have shown and it is returning blank data so not sure what the issue is.

    • Praful_Potphode's avatar
      Praful_Potphode
      Icon for Super User rankSuper User
      1 month ago

      Hi Lucy01 

      Can you share data model diagram here.

      ideally TeamAccess should be disconnected table.

      Datasheet should be connected to other tables in the model.

       

      Please give kudos or mark it as solution once confirmed.

       

      Thanks and Regards,

      Praful

      • Lucy01's avatar
        Lucy01
        Icon for Advocate I rankAdvocate I
        1 month ago

        Thank you, here is a mock up of the model view showing the different data tables. The Main Data Sheet contains the majority of information and the Second Data Sheet is a copy of the Main Data Sheet with the columns unpivoted. I appreciate that's probably a little strange, as is having a Calendar Table and a Date Table, but it was the only way I could find to process and present the data I have.

        The column names for Main Data Sheet and Second Data Sheet are the same, as are the access requirements for the Team Leaders.

        Below is also an example of how the UPN & Access sheet is laid out with the Team Leader's name, their Email/UPN and the Team they have access to.

        I appreciate any advice you can give me on how to set my row level access so that Team Leaders see the data from their teams only. Thank you