Anonymise data - maintian uniqueness; across multiple tables - in MDX

[PetyrBaelish]

I'm trying to attempt something in MDX (of which I have little experience) that anonymises data in a field - but that field appears in multiple tables and is used as a relationship, as per description below:

Student Table

Student ID --- Disbaility

David -  - - - - - Yes

Anthony - - - - No

Student Views

Student ID -- Lecture - - Minutes

David - - - - -  Lecture 1 - 5

David - - - - -  Lecture 2 - 7

Anthony - - - - -  Lecture 1 - 3

Anthony - - - - -  Lecture 2 - 8

I want the Student ID "Anthony", "David" to be replaced by something anonymous. I began by, in Edit Queries, on the Student table (I duplicated it into an "anonymous" version of the table first), using an Index to create a new column concatenating the word "Student" with the Index number so David becomes Student1, Anthony becomes Student2. But I have no idea how I can reflect this in the Student Views table (so David becomes Student1 and Anthony becomes Student2).

I have included a sample file below which is as per I've described. This is dummy data - the real data contians 1000's of students. The file below shows  what I've described above, but essentially I want my data above to look like this (in the 'anonymous' tables which are duplicates of the originals):

Student Table

Student ID --- Disbaility

Student1 -  - - - - - Yes

Student2 - - - - No

Student Views

Student ID -- Lecture - - Minutes

Student1 - - - - -  Lecture 1 - 5

Student1 - - - - -  Lecture 2 - 7

Student2 - - - - -  Lecture 1 - 3

Student2 - - - - -  Lecture 2 - 8

https://www.dropbox.com/s/wm6yadukdi2xxgb/Aononymous.pbix?dl=0

Thanks.

[v-eachen-msft]

Hi @PetyrBaelish ,

You can create a new column in the table "Student Table":

anonymous id =
"Student" & RANKX ( 'Student Table', 'Student Table'[Student ID],, DESC, DENSE )

Or use RELATED() function to get it.

Here is the result:

[PetyrBaelish]

Thanks for your response. I was hoping however to achieve this in MDX while I'm transforming the data. The full steps of what I wish to achieve are:

- I have the tables Student and Student Views [done]

- Duplicate these tables and call the new ones Student Anonymous and Student Views Anonymous [done]

- [what I'm currently trying to do] On the "Anonymous tables" replace the Student ID to something that is anonymous - and do it in a way that maintains the integrity between the tables - so when David becomes Student1 in the Student Anonymous table, all rows for David in the Student Views Anonymous will become Student1 here too.

- I will then create an additional column with static text on every table - the text will either be "Anonymous" or "Actual" on the appropriate tables.

- I will then append Student and Student Anonymous into one table, and append Student Views and Student Views Anonymous into one table.

- At this point I will finish transforming the data in Edit Queries

- I will then use Row Level Security has that users in Group A can only see the "Anonymous" data, and users in Group B can only see the "Actual" data

For that plan to happen, I believe I need to do the anonymisation in Edit Queries, in MDX, as this seems the cleanest way of hiding the actual data from those only allowed to see the anoanymous data.

Is there an MDX equivalent to the RELATED() function?