Row Level Security Errors

Hi Lucy01​ ,

Could you check the suggestion provided above and let us know if you have any additional queries?

Thanks!!

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

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.

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.

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 Well, what the code is doing is getting the user's USERPRINCIPALNAME which is generally the user's email adress. It is then getting a Team from DataSheet although not sure how that is working exactly unless there is only a single row in DataSheet?? Then it is returning a count of rows after filtering the Team Access table by comparing UserUPN to the email address and Team to the Team and checking if it is > 0. 

The way it is going about it is kind of strange honestly. Generally what I would do is to get the user email address and team and simply do a logic statement if the email equals the UserUPN and the team equals the team, then true otherwise false. Do you even need to check the team if the email addresses match?

What is the error you were getting trying to form the relationship? Can you maybe describe your data or provide some samples from the tables you are dealing with?