RLS managed ‚Deanonymization‘

[Raketenrudi]

Hi,

I need to have data aggregation on Company Level. I.e., Sales data. 

Everyone may see the total value and/or other accounts in an anonymized version. 

I tried a pseudo-anonymized approach. The main data set is 'public' with the identifier TID.

there is a translation table with the clear names and TID managed by RLS.

I build a measure in the main_ table returning the clear name if available (RLS) or 'classified'. Calculated columns do not work with RLS as far as I see it, right?

Unfortunately measures are quite messy when it comes to visuals and/or filters. 

this picture shows what I want to achieve:

... viewing as Ben.

do you have suggestions how to accomplish this? Open to new approaches as well.

thanks!!

[Raketenrudi · Accepted solution]

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. 

[Zanqueta]

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.

[cengizhanarslan]

1) Apply RLS so users only ever see their own rows

2) Precompute global averages in:

• Power Query
• SQL
• Dataflow

3) Store them in a separate table not affected by RLS

4) Use that table for the “total” values in visuals by HASONEVALUE() expression.

[danextian]

Hi @Raketenrudi 

RLS is applied at the semantic layer, so it cannot be overridden by measures. Calculated columns also do not change based on who is logged in, so that approach will not work. You will need a table that is not affected by RLS and use measures to show Ben’s name while classifying the rest. I have a YouTube tutorial that explains how to anonymize other users while keeping the name of the currently logged-in user visible. https://youtu.be/qL_p6OLDyQI 

[Raketenrudi]

Hi, 

nice approach! Though, in my case it could not work. 

as far as I know calculated tables and/or columns will use the user which is used for the daily refresh rather than the user looking at the report.. correct?

[AWD]

With RLS, I do not think you can achieve what you are looking for within a single measure pointed at the same table with RLS applied. RLS will remove the other rows from 'Ben' meaning Ben's total will only be 8.

I am also not sure if you could display 'classified' per row like that for Ben to see row level context without seeing names (short of creating multiple agregated tables which is probably not feesable)

To allow Ben (and other users) to see the total of 15 would be possible with an agregated table, however it would look something like this (i.e. missing the 'classified' rows:

Name	Amount
Ben	8
Total	15

[Raketenrudi]

Hi,

About showing single 'classified' rows: I would use TID to build the visual and was hoping to use the name  column as label. Not sure how to do that, but I am under the impression, that I have seen something like that before. Otherwise I would adapt the measure to 'use clear name if available else use TID'

Also: I tables main_ and names_ are not related. So RLS on names_ does not reduce the rows of main_

BR