Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ShivGC
Helper I
Helper I

Anonymising data so that it can be presented to clients

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 NameIndexRegionPremium
A Firm Ltd1UK100
A Firm Ltd1USA200
B Firm Ltd2UK80
C Firm Ltd3UK70
C Firm Ltd3USA100


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):

Untitled.png

 

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?

1 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. 

View solution in original post

6 REPLIES 6
mahenkj2
Solution Sage
Solution Sage

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.

 

mahenkj2_0-1686448300599.png

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

 

Please check.

 

Hope it helps.

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?

Hi @ShivGC 

 

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

 

mahenkj2_0-1687370452231.png

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.

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. 

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
Super User
Super User

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.