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

Shahid12523's avatar
Shahid12523
Icon for Community Champion rankCommunity Champion
30 days ago

Your RLS DAX fails because DataSheet[Team] can contain multiple values; use a proper Team dimension/relationship and filter Team Access with USERPRINCIPALNAME() instead.

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