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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Impactful Individual
Impactful Individual

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?

mahenkj2
Impactful Individual
Impactful Individual

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors