How to Display Personal Performance While Masking Others in Power BI

Sales teams may want to see how their performance compares to the rest of the group, but there’s a fine line between healthy competition and oversharing. As a rep, you need to know where you stand, but you don’t necessarily need visibility into everyone else’s names and the corresponding numbers. A good compromise is to show all sales performance data while keeping other reps anonymous, so you get the context without exposing personal details

 

Why not RLS? At first glance, row-level security (RLS) might seem like the right tool for this scenario. After all, it is designed to restrict what data a user can see based on their log-in credentials. In practice, though, RLS works by filtering out rows entirely. If you applied it here, each sales rep would only see their own data and nothing else – which defeats the purpose of comparison.

 

Our goal is not to hide rows but to mask identities. In this blog, we’ll look at how to set that up in Power BI.

 

We’ll be using a simple dataset: a fact table with columns for Salesperson ID, Date and Sales Amount called Sales and a dimension table with columns for Salesperson Name, Salesperson ID and Email called Employee.

 

Let’s create our first measures:

Sales = 
SUM ( Sales[Sales Amount] )

Current User = 
"[email protected]" -- replace with USERPRINCIPALNAME() to get the logged-in user's email

 

Now that we have the core measures in place, the next step is deciding how to integrate the masking logic into the model. We’ll be looking at two approaches that are similar in concept but different in execution: using a many-to-many single-direction relationship, or working with a disconnected table. In both cases, the solution relies on creating an extra table that contains the masked employee mappings.

 

The calculated table below can also be created in Power Query or further upstream at the source (preferrably).

Masked_Employees = 
-- Build a copy of Employee with a precomputed masked label
VAR _tbl =
    ADDCOLUMNS (
        Employee,
        "Masked Employee Name", "Emp" & FORMAT ( RANKX ( Employee, [Salesperson ID],, ASC, DENSE ), "000" )
    ) -- Generate one row for each combination of logged-in employee × masked mapping
VAR _inner =
    GENERATE (
        -- Outer: the logged-in user's perspective (one row per employee)
        SELECTCOLUMNS (
            _tbl,
            "Logged-in user's name", [Salesperson Name],
            "Logged-in user's email", [Email],
            "Masked Employee Name_", [Masked Employee Name]
        ),
        -- Inner: the masked mapping to be cross-joined per outer row
        SELECTCOLUMNS (
            _tbl,
            "Email - others", [Email],
            "Masked Employee Name",
                IF (
                    -- If the inner email matches the outer (logged-in) email,
                    -- show the real name (optionally prefixed with a zero-width char so it sorts first)
                    [Email] = [Logged-in user's email],
                    UNICHAR ( 8203 ) & [Logged-in user's name],
                    -- Otherwise show the masked label from _tbl
                    [Masked Employee Name_]
                )
        )
    ) 
VAR _outer =
    -- Clean up columns for the final ouput
    SELECTCOLUMNS (
        _inner,
        "Logged-in user's name", [Logged-in user's name],
        "Logged-in user's email", [Email - others],
        "Email - others", [Logged-in user's email],
        "Masked Employee Name", [Masked Employee Name]
    )
RETURN
    _outer

 

The unicode character in the line below is optional but it ensures that that the name of the logged-in user will always be first if sorted in ascending order. 

UNICHAR ( 8203 ) & [Logged-in user's name]

 

 

Using a many-to-many single-direction relationship

Create this measure:

Masked Sales = 
VAR _CurrentUser = [Current User]

RETURN
    CALCULATE (
        [Sales], 
        -- Restrict calculation to only the rows in Masked_Employees 
        -- that match the logged-in user's email
        KEEPFILTERS (
            Masked_Employees[Logged-in user's email] = _CurrentUser
        )
    )

 

The filter in the measure above will filter the rows to match the logged-in user’s email. For example, if Charlie Brown is logged in, the visual will show only the rows which value in column Logged-in user’s email column match the value of [Current User] measure. Once Masked Employee Name column and [Masked Sales] measure are added to the visual, they should look like below:

 

Using a disconnected table

Duplicate Masked_Employees table by creating another calculated table and referencing it.

Masked_Employees_Disconnected = 
Masked_Employees

Create this measure:

Masked Sales - Disconnected = 
VAR _CurrentUser = [Current User]

-- Get the list of masked employee(s) associated with the logged-in user
VAR _Users =
    CALCULATETABLE (
        VALUES ( Masked_Employees_Disconnected[Email - others] ),
        -- Restrict to the row(s) where the logged-in user matches
        KEEPFILTERS ( Masked_Employees_Disconnected[Logged-in user's email] = _CurrentUser )
    )

-- Apply the masked mapping so that the logged-in user only sees sales
-- for the employees they are mapped to (through TREATAS filter)
RETURN
    CALCULATE (
        [Sales],
        KEEPFILTERS ( TREATAS ( _Users, Employee[Email] ) )
    )

Once Masked_Employees_Disconnected[Masked Employee Name] column and [Masked Sales – Disconnected] measure are added to the visual, they should look like below, still very similar to the other approach:

Which method should you choose? It depends. Tests in DAX Studio showed mixed results for both approaches, likely because the dataset was small. It boils down to not just performance but also maintainability. To better understand the trade-offs,  refer to the table below:

Approach	Pros/Cons	Details
Disconnected Table + TREATAS	Pros	Explicit control over when the mapping/filter is applied Less risk of unintended filter propagation Affects only chosen measures
	Cons	Requires extra DAX Every measure that needs masking must include the logic (calculation groups may come in handy for this) Slightly more complex to maintain
	Pros (Performance)	Low overhead for small mapping tables Filter cost is applied only for measures that use the mapping
	Cons (Performance)	Scales poorly as mapping table grows very large because TREATAS builds a virtual filter table per query Repeated application across many measures increases cost
Many-to-Many Relationship	Pros	Cleaner DAX across measures (no TREATAS) Mapping enforced more 'natively' in the model Easier to apply masking widely without changing every measure
	Cons	Active relationships can produce unexpected filter propagation Model-level filters affect all queries unless carefully modeled
	Pros (Performance)	Scales better with large mapping tables because the storage engine optimizes joins and relationships Avoids repeated formula-engine filter construction for each measure
	Cons (Performance)	Relationship filters are evaluated for many queries which can add overhead Complex relationships can increase query plan complexity and memory use

 

A video version of this blog is available on YouTube - https://youtu.be/qL_p6OLDyQI