Founder | CEO @ RADACAD | Coach | Power BI Consultant | Author | Speaker | Regional Director | MVP
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.
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.
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.
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.
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.
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.
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.
Set LOOKUPVALUE’s alternate result to 0 so an unlisted user is treated as unauthorized and receives the protected result.
Yes. The demonstrated approach is for users with view access; users with edit access to the semantic model can still see the underlying data.