Anonymising data so that it can be presented to clients

[ShivGC]

Hi, 

I would like to produce a power BI report that allowed clients to see themselves on the various different charts created, but they could only see their competition as 'Peer 1', 'Peer 2', 'Peer 3' and so on...

So, the problem is the following:

1st Table 

Client Name

A Firm Ltd

B Firm Ltd

C Firm Ltd

2nd Table

Client Name	Index	Region	Premium
A Firm Ltd	1	UK	100
A Firm Ltd	1	USA	200
B Firm Ltd	2	UK	80
C Firm Ltd	3	UK	70
C Firm Ltd	3	USA	100

These two tables are created in Power Bi, and have no relationship between them. 

A clustered bar chart is created where client is on the y-axis and premium is on the x-axis. The problem here is that each clients name can be seen, and therefore this can not be presented to clients as they can see everyones data. So, a filter is created on page 1 of the dashboard which allows you to pick which client you are working on (e.g A Firm Ltd), and then on the graphs the competition is anonymised. Here is the expected output (apologies for the bad drawing):

Could you please explain the following:

1) Can we do this simply with Dax code, by creating a 'New Measure' or 'New Column'?

2) How would the Dax code look (or alternative solution), could this be explained as simple as possible?

3) Ideally, I would like the peer number to change, everytime so the market information is truly anonymised. For instance, Peer 1 and Peer 2 would switch everytime a new client is picked so the data is truly anonymised. Thus, peer 2 will not always be assigned  to C Firm Ltd in the example above. (In my actual data set I have more informaiton). Is this possible?

[mahenkj2]

Hi @ShivGC ,

I have tried to acheive what you need, it is done with a random number generation in power query and some simple calculated columns. There are two simple disconnected hidden tables, and final solution it should fulfill your needs:

Everytime you refresh data whether or not data changes, Peer name will randomly change except your company. In one of the disconnected table, you have to set value of Org name which will use this file.

https://drive.google.com/file/d/1tV4kx3UpHXSFM29QgzGyfGNzwNbup8pH/view?usp=sharing

Please check.

Hope it helps.

[ShivGC]

Hi, Thank you for your response. However, I do not believe this is something that would work for myself. 

Here are the problems I find with it:

1) I wanted to be able to change the selected organisation. This will not be a fixed client, for instance, today it may be A Firm Ltd, but tomorrow I will be presenting to B Firm Ltd and so this selection will need to change. Prefferably, i would like to select the client I am working with, in the power bi dashboard and this would lead to a randomisation of the peer names.

2) I do not understand how the peer names are random, because this is assigned to an index, and so it has a now got an assigned value to it. For instance, each Firm in the example above is assigned an index, and each index is assigned a peer name (peer 1, peer 2, or peer 3), these values will not change upon selecting a new client as explained in point 1. Essentially, I would like the peer 1, and peer 2 to be changing each time a new client is selected so that no one will ever know who peer 1 actually belongs too. 

I hope this makes sense?

[mahenkj2]

Hi @ShivGC 

1. You just need to change highlighted column here for your client which you wish to make visible:

As of now I kept, The report header fixed, but that too you can make dynamic by using the same table/measure etc. I tried to present you a possible concept, please work out more.

2. I believe, the peers come random. Whenever a new client is added, you will have to at least refresh the report to see this new client, that time this random behaviour will help to change peers. Try it.

Hope it helps.

[ShivGC · Accepted solution]

Hi 

Thank you for your response. I have found an easier way to do what I was looking for. In essence, I have created a new measure that utilises an if statement. This is how it looks. 

1) Selected Client = var selectedClient = SELECTEDVALUE('Client (Distinct)'[Client])

return SWITCH(SELECTEDVALUE(Programme[Client]) = selectedClient, TRUE, "DarkBlue" , "LightBlue")

2) Then, I created a graph and went to the visualisation tab > Format Visuals > Columns > and clicked 'fx' to base the columns on the measure created above. 

3) Then, I got rid of the client axis, so the client are not seen and this is completely random now. 

I believe you basically did the same thing, but went a step extra to try create a random peer axis, I realised this was no longer needed. 

[lbendlin]

I think we need to talk about the difference between "presenting" and "sharing". If you ever only want to show them the data on the screen of your PC but are not planning to share the link with them so they can look at the data themselves then you can achieve what you want.  However it will make for a frustrating experience on their side.

[lbendlin]

1) No.  You need to use Row Level Security or individual reports (one by client)
3) You can randomize with RLS