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

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

Hi Lucy01​ 

You don't necessarily need a relationship between Team Access and your DataSheet. In fact, for this type of dynamic RLS, I'd keep Team Access disconnected and use it only to determine which teams the current user is allowed to see.

For example, if DataSheet[Team] is the column that ultimately filters your report data, you could put the RLS rule on DataSheet:

VAR CurrentUser =

    LOWER ( TRIM ( USERPRINCIPALNAME () ) )

 

VAR AllowedTeams =

    CALCULATETABLE (

        VALUES ( 'Team Access'[Team] ),

        FILTER (

            ALL ( 'Team Access' ),

            LOWER ( TRIM ( 'Team Access'[UserUPN] ) ) = CurrentUser

        )

    )

 

RETURN

    'DataSheet'[Team] IN AllowedTeams

The logic is basically:

Current user → find their allowed teams in Team Access → keep only those teams in DataSheet → relationships then filter the rest of the model.

This also means you don't need to compare the team on every row against Team Access manually.

One important thing to check is the model relationships downstream from DataSheet. DataSheet[Team] needs to be on the appropriate dimension/filtering side so that the RLS filter can propagate to your fact tables.

If you're still getting completely blank results with View as → Other user, I'd check the exact value returned by USERPRINCIPALNAME() and whether it exactly matches Team Access[UserUPN] after trimming/lowercasing. A small mismatch there would cause AllowedTeams to be empty.