Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Let's say I have a table of external users from different clients:
Name | Client | |
David Tennant | david.tennant@who.com | Ten |
Matt Smith | matt.smith@who.com | Eleven |
Peter Capaldi | peter.capaldi@who.com | Twelve |
Jodie Whittaker | jodie.whittaker@who.com | 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.
Solved! Go to 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
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
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 ?
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.
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.
Hi, were youable to solve this to display unmasked data for certain users?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |