Forum Discussion
RLS if-statement on USERPRINCIPALNAME
Hi Anonymous ,
In Manage roles, you can create new roles and try this DAX pression to solve your problem:
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[email] = USERPRINCIPALNAME () && [IsTeamlead] )
) > 0,
[Country]
= CALCULATE (
MAX ( 'Table'[Country] ),
FILTER ( 'Table', 'Table'[email] = USERPRINCIPALNAME () )
),
[email] = USERPRINCIPALNAME ()
)
When you use View as roles, you will get the following result:
Here is the demo , please try it:
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your suggestion.
Every row (employee) has [email]. Your solution is a type Option A || Option B. Will Option B ever be evaluated if Option A is true?
The statement will evaluate once for each row in the table. But if the first part evaluates to TRUE the second part is not evaluated for that row (since all users should have access to the row that contains their own email, right?).
I tried your code, and it was as I expected. The first statement will evaluate true, since whenever someone logs in with the USERPRINCIPALNAME(), it will match a row. I believe the second part is never evaluated.
I tried it (on my own table with other columns) and it looked like it worked for me. You must understand that when the user logs on the statement will be evaluated once for each row in the table. If there is a row with a non-matching email column, the first part of the expression will ecaluate to FALSE. The second part will then look for rows where email is the same as USERPRINCIPLENAME, IsTeamLead = TRUE and the same value in the country column as the current row being evaluated. If a row like that exists (COUNTROWS returns > 0) the expression will return TRUE.