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

Lucy01's avatar
Lucy01
Icon for Advocate I rankAdvocate I
18 days ago

Thank you, could you please explain a little more about the dimension/relationship, I'm not sure how to approach that?

v-sathmakuri's avatar
v-sathmakuri
Icon for Community Support rankCommunity Support
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!!