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

ShahRukhSameer's avatar
ShahRukhSameer
Icon for Continued Contributor rankContinued Contributor
30 days ago

Hi Lucy01​,

I think the issue is mainly with where the RLS filter is being applied. I wouldn’t try to relate the Team Access Excel table to all of your report tables.

If you have a Team/DimTeam table, keep Team Access disconnected and apply the role to DimTeam instead:

VAR CurrentUser = LOWER ( TRIM ( USERPRINCIPALNAME() ) ) VAR UserTeams = SELECTCOLUMNS ( FILTER ( ALL ( 'Team Access' ), LOWER ( TRIM ( 'Team Access'[UserUPN] ) ) = CurrentUser ), "Team", TRIM ( 'Team Access'[Team] ) ) RETURN TRIM ( 'DimTeam'[Team] ) IN UserTeams

Make sure DimTeam has a normal 1-to-many relationship with your fact table.

The problem with the original code is this part:

VAR CurrentTeam = TRIM ( 'DataSheet'[Team] )

You're trying to get a single team from the data table while the RLS is being evaluated, which can result in an invalid/multiple-value context.

The idea is simply: get the logged-in user's email → find the teams assigned to that user in Team Access → filter DimTeam to those teams.

If you don't currently have a separate Team dimension, I'd create one first. It will make the RLS much easier to manage.