Power BI: mask sensitive values by user
Power BI

Power BI: mask sensitive values by user

by HubSite 365 about Reza Rad (RADACAD) [MVP]

Founder | CEO @ RADACAD | Coach | Power BI Consultant | Author | Speaker | Regional Director | MVP

Power BI developers can show sensitive column values to approved viewers while masking them for others in the same report.

Key insights

  • A calculated column set to User Context can use USERPRINCIPALNAME() to identify the report viewer.
  • A user table can map email addresses to a CanViewSensitiveData permission flag.
  • The protected column can be used in slicers, including search, while restricted users see a masking message.
  • Hide the original sensitive field and use the protected column in reports.
  • Users with edit access to the semantic model can still see the underlying data.

Power BI user-context-aware calculated columns can show a sensitive value to approved users and a protected message to others in the same report. This approach is for Power BI developers, data modelers, and report owners who need to tailor column values to the viewer without relying on RLS, CLS, or OLS for this scenario. Reza Rad (RADACAD) [MVP] demonstrates the pattern with a customer credit card column and a permission table.

What's new

A standard calculated column does not normally change its result for each report viewer. A user-context-aware calculated column adds an Expression Context property that lets the expression respond to the current user. Set that property to User Context and the column can use DAX functions such as .

The example uses a customer table with a physical credit card number field. A separate user table maps a Power BI user’s email address to a value. The calculated column looks up the current viewer, then returns the real number when the permission is 1, or text such as “sensitive data is protected” otherwise.

This is useful when the report must remain functional for both groups. With column-level security, the demonstrated scenario produced errors in visuals for users without access to the protected column. The user-context-aware column instead returns a display value for each viewer, so the same report can use it in a table and a searchable slicer.

Who it is for and what you need

The pattern is aimed at semantic-model authors building reports for users with different permissions to a particular field. The demonstration applies to users with view access to the semantic model, including people consuming reports or connecting through Power BI Live Connection or Analyze in Excel. It also discusses paginated reports built on the model.

You need a user permission table in the model, with the viewer’s account identifier and a flag describing whether that person can see the sensitive value. The table needs to include the relevant Power BI users and be maintained as permissions change. The example hides this technical table from report consumers.

In the demonstration, User-context-aware calculated columns are a preview feature. In Power BI Desktop, the feature must be enabled under Options and settings > Options > Preview features. The demonstration notes that this switch will no longer be needed once the feature is generally available; check the setting in your Desktop environment before building the model.

How it works

  1. Create or load a permission table containing user email addresses and a 0-or-1 permission field, such as . Keep the permission values current.
  2. Hide the original sensitive field, such as the physical credit card number column, from the report field list. Create a new calculated column in the customer table for the report-facing value.
  3. In Model view, select the calculated column and open its Advanced properties. Change Expression Context from the standard context to User Context.
  4. Write a DAX expression that uses to find the current user’s permission in the user table. Use as the account to match, and set the alternate result to 0 so an unlisted user is treated as unauthorized.
  5. Return the original credit card value when the permission is 1; otherwise return a protective text value. Use this new column—not the original field—in report visuals and slicers.
  6. Publish and test the report as both an authorized and an unauthorized viewer. Check the displayed values, slicer search, and exported output under each identity.

The key distinction is that the column is evaluated in the context of the user making the query. A measure could display conditional text in a visual, but the calculated column behaves like a column in the model and can be used in a slicer, including its search box.

For more on the product area, see our Power BI coverage.

Limitations and alternatives

This technique masks values for viewers; it is not a substitute for controlling access to the semantic model itself. The demonstration explicitly warns that users with edit access to the semantic model can see the underlying data regardless of this calculated-column pattern. Treat model-edit permissions separately from report view access.

The original sensitive field also remains in the model, so hide it and ensure reports use the protected replacement. Maintain the permission table carefully: an incorrect or stale entry can produce the wrong result. Testing should include an account that is absent from the table, because the example deliberately defaults that lookup to no access.

Use this approach when viewers need the same report structure but different values in a field. RLS filters rows, while this pattern conditionally returns a column value. CLS can secure a column, but in the demonstrated report it caused visual errors for users who lacked access. Consider the required boundary: this pattern masks displayed values, while semantic-model edit access remains unrestricted by it.

Sources

Frequently asked questions

How can I mask a Power BI column for some users?

Create a calculated column with Expression Context set to User Context. Use USERPRINCIPALNAME() to look up the viewer’s permission and return the sensitive value only when authorized.

Can users with permission search the protected value in a slicer?

Yes. The protected calculated column can be used in a slicer with search. Authorized users see and search the real values; other viewers receive the protected result.

What happens if a user is not in the permission table?

Set LOOKUPVALUE’s alternate result to 0 so an unlisted user is treated as unauthorized and receives the protected result.

Can semantic-model editors see the original sensitive data?

Yes. The demonstrated approach is for users with view access; users with edit access to the semantic model can still see the underlying data.