Forum Discussion
RLS managed ‚Deanonymization‘
Hi all,
in the end it was a mix of all - and I am not really at the end yet. So far:
- I created a randomized ID in the main query which changes with each daily refresh. RandomID is the basis of all visuals.
- Names_ is still under RLS and unrelated to main_
- in Main_ I added a measure 'Name' returning the clear name (I.e., Ben) when available in names_, else returning classified
- Example bar chart: use RandomID for category axis. Hide Axis. Use clear name as data label ✅
works well since, RandomID is really hard to track back AND all visuals work fine due to RandomID being a column and not a measure.
Hi @Raketenrudi, as danextian and AWD , already mentioned, RLS only applies a row‑level filtering context. However, if you were under pressure and needed an urgent workaround, we can try follow these steps:
1. Create the two tables without relationships, including an email column for RLS based on USERPRINCIPALNAME()
2. Use the TID field from MAIN_ as the basis of the visual
Create a table or matrix visual and place:
Rows: MAIN_[TID]
Values: the measures you will create in the next step (for example, Display Name, SUM(MAIN_[Val1]))
If needed, you may later hide the TID row header so that only names or “Classified” appear.
3. Create the required DAX measures: My IDs, My Name, and Display Name
The following measures support the RLS logic where:
Regular users see only their own TID in NAMES_, and therefore their own name is revealed.
Users without RLS (for example administrators) see multiple or all TIDs in NAMES_, and therefore all values remain anonymised.
3.1. Measure: My IDs
This returns the user’s own TID if RLS restricts them to one row. If more than one row is visible (meaning the user has unrestricted access), it returns BLANK, ensuring no de-anonymisation occurs.
My IDs :=
VAR VisibleTIDs =
CALCULATETABLE (
VALUES ( NAMES_[TID] )
)
VAR CountTIDs =
COUNTROWS ( VisibleTIDs )
RETURN
IF (
CountTIDs = 1,
MAX ( NAMES_[TID] ),
BLANK ()
)
3.2. Measure: My Name
Returns the real name of the current user only when the RLS context restricts NAMES_ to exactly one row. Otherwise returns BLANK.
My Name =
VAR VisibleName =
CALCULATE (
MAX ( NAMES_[Name] ),
ALL ( NAMES_ ) -- respeita RLS, mas remove outros filtros
)
RETURN
VisibleName
3.3. Measure: Display Name
This determines what is shown on each row of the visual. If the row belongs to the current user, it reveals their name; otherwise it displays “Classified”.
Display Name =
VAR VisibleName = [My TIDs]
var SelectedID = SELECTEDVALUE(Main_[TID])
var _Total = HASONEVALUE(Main_[TID])
RETURN
SWITCH(TRUE(),
_Total = FALSE(), BLANK(),
VisibleName = SelectedID,[My Name],
"Classified")
4. Create the RLS rule based on the NAMES_ table
Go to Model view → Manage Roles
Create a role, for example, RLS_Users
On the NAMES_ table, apply the following filter:
DISCLAIMER: While I wrote a draft of this answer, I used Copilot to create a longer, more detailed step-by-step description to make it easier to apply.
The file is attached.