cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

How to Show One Customer Against a List of their Anonymous Competition

I am trying to create a scatter plot in which I can select one customer from a list of customers and have only the selected customer's name appear while the rest show up as "Other". The result would be used to define the legend in the scatter plot, with the selected customer appearing in a given colour and "Others" in a second colour. The purpose of this is to show to a particular customer how they stack up against their competition, without revealing the identity of their competition. 

 

I have created a manual version of what I'm attempting to do, but the customer name is hard coded in and is not easily changeable based on user input:

 

 

6-9-2017 3-09-14 PM.png

 

What I have tried so far without success:

 

From the original list of Customers in Table 1 I created a second list in a new table and left the two tables unlinked. 

Customer Non-Linked = SUMMARIZE(ALL('Table1'), 'Table1'[Customer])

I use the unlinked table as a slicer to be able to select the customer that I want to highlight. If I were to use the original table I would filter out all the other customers and would be left with only one dot on my scatter plot.

 

I then created a measure that I can use as my selected customer for further calculations... I'm pretty sure this is where my issues start:

Selected Customer = IF(HASONEVALUE('Customer Non-Linked'[customer Non-Linked]), FIRSTNONBLANK('Customer Non-Linked'[Customer Non-Linked],'Customer Non-Linked'[Customer Non-Linked]), "Make Selection")

From there I created a new column in Table1 called Anonymous Customer with the intent of populating it with the name of the customer I've chosen and "Other" in place of all the other customer names I want to make anonymous:

Anonymous Customer = IF('Table1'[Customer] = [Selected Customer],[Selected Customer],"Other")

My result is a column full of "Other", regardless of which Customer Non-Linked I select in the slicer. I'm not sure if I'm even on the right track with this, or if there is another way to go about this process, any help is much appreciated!

 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @Samboko,

 

Current power bi not support to create a dynamic calculate column/table based on slicer. Measure can dynamic changed by slicer, but legend field not support, so I'd like to suggest you use query parameter to achieve your requirement.

 

Steps:

1. Use column from original table to create new query list.

2. Create a parameter with above list.

3.  Add a custom column to mark the select item in power query.

 

Aftet above steps, you can modify the parameter to modify the selected tag.

StepsStepsResultResultModifyModify

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support
Community Support

Hi @Samboko,

 

Current power bi not support to create a dynamic calculate column/table based on slicer. Measure can dynamic changed by slicer, but legend field not support, so I'd like to suggest you use query parameter to achieve your requirement.

 

Steps:

1. Use column from original table to create new query list.

2. Create a parameter with above list.

3.  Add a custom column to mark the select item in power query.

 

Aftet above steps, you can modify the parameter to modify the selected tag.

StepsStepsResultResultModifyModify

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Thank you Xiaoxin, I followed your instructions and was able to get it to work. I made one tweak when creating the custom column in that I put:

 

If [Month] = Select then [Month] else "Other"

 

This way I get the name of the target month (or in my case customer) to display in the legend.

 

The two downsides to this way of doing it is that its a bit slow switching between parameters since it needs to refresh the entire query, which in my case is about 10mb. The other is that I can't switch between customers once I've loaded it as a report to Power BI online. Neither of which are show stoppers for now but if anyone has another way to solve this I'd be curious to see how.

 

Thanks,

 

Sam

Hi @Samboko,

 

Based on your screenshots, i think highlight slicer will be suitable, but current slicer not support to highlight other visuals, I think you can vote below ideas which has the similar requirement:

Slicer can have highlight interaction on charts

 

In addition, you can turn off the refresh option and manual refresh it in power query to increase performance of apply change.

4.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft Thank you for all your feedback and solutions, with your solution I can get done what I need and hopefully the new features will enable further refinement of the model.

 

Cheers.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors