Anonymizing certain data / Dynamic data masking

[ryanem]

Hello and thank you in advance for reading. I'm trying to share performance data with team members. I can create a bar chart with Names on the Y axis and then the Performance Metric on the X axis, however I would like each person to be blinded to the names of the other teammates (yet still see the individual performance metric value of each one of those anonymous colleagues).

I've spent a couple hours trying to do this with copilot/chatgpt but have been unsuccessful. 

First I did RLS, but this ended up just filtering for the individual's data and they can't see anyone elses.

Then there was an attempt by creating a calculated column -- unsuccessful because USERPRINCIPALNAME() can't be used in a column. Then there was an attempt by creating a DAX measure -- unsuccessful.  Then there was an attempt by using the 'Deneb' visual add-in -- unsuccessful. 

I thought maybe if I had a separate Excel tab for each teammate which had all of the data, their name, and then everyone else's name as "anonymous" I might be able to do something (almost like a custom report for each person), but that doesn't seem to get me anywhere--I can't figure out how to automatically generate a report for each person.

I'm open to any advice. Thank you so much!!!

[v-karpurapud]

Hi @ryanem 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to @Royel and @danextian  for offering helpful suggestions.

Could you let us know if the suggested solution resolved your issue? This information can assist other community members facing similar challenges.

Thank you.

[danextian]

Hi @Royel 

You can follow @Royel’s approach if the goal is to mask data in a table or matrix visual. However, when the masked data must function as a dimension, you’ll need a more creative method. This would involve creating a table that contains all possible combinations of the current user’s email with the emails of other users. If the two email columns from this crossjoin match, return the actual username; otherwise, return something like Employee xxxx - the rows to return depend on the logged in user.

The attached PBIX file contains two approaches. In my testing, the disconnected table method performs faster, though actual results may vary depending on your semantic model.

[Royel]

Hi @ryanem thank you posting this interesting problem. 

Lets try to achieve this with help of Power Query and Dax

Here is sample data: Update your email address 

EmployeeID	Name	Email	Performance Score	Department
1	Royel	myemail@domain.io	85	Sales
2	Sarah	other@domain.io	92	Sales
3	Mike	other1@domain.io	78	Sales
4	Lisa	other2@domain.io	88	Sales

Save this data into a csv file and the go to power query create a blank query and pest this m-code 

let
    // Step 1: Load your source data
    Source = Csv.Document(
        File.Contents("D:\Community\Dynamic Data Masking\Employee_Performance_Data.csv"),
        [Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]
    ),
    
    // Step 2: Promote headers and set data types (FIXED: was EmployeeData, now Source)
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{
        {"EmployeeID", Int64.Type}, 
        {"Name", type text}, 
        {"Email", type text}, 
        {"Performance Score", Int64.Type}, 
        {"Department", type text}
    }),
    
    // Step 3: Add Index column starting from 1
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 1, 1, Int64.Type),
    
    // Step 4: Create anonymous name pattern
    AddedAnonymousName = Table.AddColumn(AddedIndex, "Anonymous Name", 
        each "Employee " & Text.From([Index]), type text),
    
    // Step 5: Create department-based anonymous names (optional)
    AddedDeptAnonymous = Table.AddColumn(AddedAnonymousName, "Dept Anonymous Name", 
        each [Department] & " Employee " & Text.From([Index]), type text),
    
    // Step 6: Create consistent sorting order (important for anonymization)
    SortedData = Table.Sort(AddedDeptAnonymous,{{"EmployeeID", Order.Ascending}}),
    
    // Step 7: KEEP ALL COLUMNS for DAX solution (don't remove Name and Email)
    // We need Name and Email for USERPRINCIPALNAME() logic in DAX
    FinalColumns = Table.SelectColumns(SortedData,{
        "EmployeeID", 
        "Name", 
        "Email", 
        "Anonymous Name", 
        "Dept Anonymous Name",
        "Performance Score", 
        "Department"
    })
in
    FinalColumns

Note: You need to update the source 

Now, Lets write a dax to get the Display Name not users name or email address 

Dax Measure: 

Display Name = 
VAR CurrentUser = USERPRINCIPALNAME()
VAR SelectedEmail = SELECTEDVALUE(EmployeeData[Email])
VAR ActualName = SELECTEDVALUE(EmployeeData[Name])
VAR AnonymousName = SELECTEDVALUE(EmployeeData[Anonymous Name])
RETURN
IF(
    SelectedEmail = CurrentUser,
    ActualName & " (You)",
    AnonymousName
)

Create a Table visual: with these columns 

As you noticed, its making all users records as Anonymous (Employee 2,3,4) expcept current user (my name). 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!