Row Level Security - Spoof other user if not in list of valid users

[OobuJoobu]

I have a table called Manager_Hierarchy containing the field "email" which is used to compare to the built in variable userprincipalname() so I can filter results by the person signed on.

In the "Manage roles" section for row level security I have this line:

[email] = userprincipalname()

this sets a role called user_email, and for all our registered managers this is working well.

One of the users of the report (let's say their email address is fred.smith@mycompany.com) gets a particular view that we would like to replicate to anyone not in the Manager_Hierarchy table (it's a quite generic view that we'd be happy for non managers to see).

So I'm trying to find a way to get the DAX expression in Manage Roles to say "if userprincipalname() doesn't appear in the Manager_Heirarchy table, then set the userprincipalname() name to fred.smith@mycompany.com ".

I'm new to DAX expressions so if anyone can help with how I could write that it would be appreciated!

[v-zhenbw-msft]

Hi @OobuJoobu ,

Do you want to show the fred.smith’s data if the user doesn’t exist in Manager_Hierarchy table?

If yes, you can create a new column in your fact table. Please refer the following steps.

1. Create a new email column in fact table. Replace the unmatched email with the email you want to configure.

Here we use E to replace Fred.smith.

new Eamil = 
var _email = CALCULATE(MAX(Manager_Hierarchy[Email]),FILTER(Manager_Hierarchy,Manager_Hierarchy[ID]='Fact'[ID]))
return
IF(
    ISBLANK(_email),"E@email.com",_email)

2. And we need to re-build a relationship based on new Email.

3. Then we can configure the RLS in Fact table. When we sign up using F@email.com, it will show the data in E@email.com.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[collinq]

Hi @OobuJoobu ,

You probably need to post this in the DAX commands forum and not in service.  That said, I did find this thread that seems similar.

https://community.powerbi.com/t5/Desktop/DAX-Code-for-Dynamic-RLS/td-p/807960

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!