Masking names based on RLS/current user

[olimilo]

Let's say I have a table of external users from different clients:

Name	Email	Client
David Tennant	[email protected]	Ten
Matt Smith	[email protected]	Eleven
Peter Capaldi	[email protected]	Twelve
Jodie Whittaker	[email protected]	Thirteen

If say Matt Smith of Eleven opens the report, how could I transform the following data table to a masked version?

Client	ScheduledDate
Ten	1/1/2018
Eleven	1/1/2018
Twelve	1/2/2018
Thirteen	1/3/2018
Ten	1/4/2018
Eleven	1/6/2018
Twelve	1/4/2018
Thirteen	1/4/2018
Ten	1/7/2018
Eleven	1/8/2018
Twelve	1/9/2018
Thirteen	1/10/2018

Masked client when users from Eleven opens the report:

Client	ScheduledDate
Client-1	1/1/2018
Eleven	1/1/2018
Client-2	1/2/2018
Client-3	1/3/2018
Client-1	1/4/2018
Eleven	1/6/2018
Client-2	1/4/2018
Client-3	1/4/2018
Client-1	1/7/2018
Eleven	1/8/2018
Client-2	1/9/2018
Client-3	1/10/2018

I tried using USERPRINCIPALNAME in a calculated column but I'm getting an error that I can't use it in a calculated column.

Client (e) = IF([Client] <> [UserOrg], "Other Clients", [UserOrg])

Any ideas how to do this? Thanks.

Edit: I'm using an Excel file as a source for all datasets. I'm not sure if SQL Dynamic Data Masking can work with this.

[quentin_vigne · Accepted solution]

Hi @olimilo

What you could do is add a role "Client" in the Row Level Security

and in the area where you can type just add : [Email]=USERPINCIPALNAME() (It will work while consulting the report on app.powerbi.com)

And filter all of your table with this conditions, should work

EDIT : Didn't see the last table.

You probably need to add an alias to your Client table like "Client-1" etc... and theses aliases will only be displayed when another client is connected to the report, so your first column would look like that : 

-- IF([Email]=USERPRINCIPALNAME() ; Client ; Alias)

- Quentin

[Anonymous]

How is that meant to be implemented? USERPRINCIPALNAME() can not be used in a calculated column, which is what i thought I would do, to determine whether to show a customer code or an Alias.

Anyone can share light on what am i missing here , around that solution ?

[Goofr]

You can't use it in a calculated column because it is calculated, it is not dynamic like DAX is.

The calculated column gets it's value when the model is loaded.
DAX values are dynamically built on the moment they are being displayed.

[Anonymous]

Can you please elaborate on the solution?

I've currently set up role based security with 2 levels - 

1) a management level which should see all data, unmasked

2) a user level who sees masked data, with the exception of their own.

[Anonymous]

Hi, were youable to solve this to display unmasked data for certain users?