Change color of scatter depending on selection from slicer

[Bastienlin_]

Hello,

I need the dots colors of my scatter plot to be dynamic based on the selection of the slicer.
My slicer is from a dimension table "REF_Entity" that is joined to my fact table containing my datas.

I have already seen the solution where i put a slicer from a duplicated table that is independant to my original tables. (link: Solved: Scatter chart with Dynamic, Conditional, Advanced ... - Microsoft Power BI Community)

But here, my use case needs me to keep using my slicer from my dimension table, linked to my fact table.


Do you have any other solution that fits my use case ?
Thank you !

[PaulDBrown]

You can use the independent table method and synch it to your dimension table slicer. You can see the setup half way down this thread:

https://community.powerbi.com/t5/Desktop/Filtering-a-value-in-a-slicer-and-coloring-it-by-different-... 

[Bastienlin_]

Hello @PaulDBrown, your setup is interesting, but the slicers don't seem to be synchronised when my slicer has multiple fields in it. For example if my slicer has fields like "Continent" then "Country" then "City".

I tried your method by just using 1 field (City) and it works, but it doesn't work in my use case where I have to use these 3 fields in the same time in 1 slicer.
Do you have an idea on why it isn't working when I have multiple fields in my slicers ?

Thank your for your help !

[PaulDBrown · Accepted solution]

See if this works (which is actually much simpler than using a hidden slicer). 

This is the setup for this example. There is a fact table, a dimension table for date and location, and the unrelated duplicate of the location dimension using:

To set up the highlighting visual, you need these two measures:

A) to calulate the values displayed, I'm using measures follwoing this pattern:

Sales for unrelated visual =
CALCULATE (
    [Sum Sales],
    ALL ( 'Location Table' ),
    TREATAS ( VALUES ( 'Unrelated locations'[Unrelated City] ), fTable[City] )
)

and to use as the conditional formatting for the columns in the unrelated table:

Colour CF =
IF (
    ISFILTERED ( 'Location Table' ),
    IF (
        MAX ( 'Unrelated locations'[Unrelated City] )
            IN VALUES ( 'Location Table'[City] ),
        "Red"
    ),
    "Blue"
)

Set up the visual you wish to highlight values using the fields from the unrelated table and the [Sales for unrelated visual]

and you will get:

Sample PBIX attached

[Bastienlin_]

@PaulDBrown 

Sales for unrelated visual =
CALCULATE (
    [Sum Sales],
    ALL ( 'Location Table' ),
    TREATAS ( VALUES ( 'Unrelated locations'[Unrelated City] ), fTable[City] )
)

• Could you please explain me the "TREATAS" trick that you have used ? 
  
  
• "Treatas" will take all filters in 'Unrelated locations'[Unrelated City] and apply the same to fTable[City] in your measure, but we have not applied any filters.
  I am wondering if we consider in the histogram that every bars have its filter ?
  For example, the bar UK can be considered that we applied a filter
  'Unrelated locations'[Unrelated Country] = UK.
  same for mexico it would be 
  'Unrelated locations'[Unrelated Country] = Mexico. etc.
  
  Thank you for your answers ! It helps me a lot !

[PaulDBrown]

TREATAS is a function that basically establishes a virtual relationship between two tables. In this case, in effect with TREATAS,  'Unrelated locations'[Unrelated City] is filtering the ftable[City]. So following your example:

'Unrelated locations'[Unrelated City] = UK will filter the fTable to return the rows where fTable[City] = UK. Since the country and continent are present in the fTable for each row, the filter propagates to these columns. Make sense?

[Bastienlin_]

Hello
@PaulDBrown, I still have few questions:

1. Instead of using TreatAs, couldn't we just create a relationship between Unrelated locations and fTAble like this ?
   
2. And what is the point on using the first method with the hidden slicer, as the second method seems simplier ?  With the hidden slicer it only worked when I put a single field in my slicer. As soon as i put more than one field, the highlighting stopped to work.

[PaulDBrown]

"Instead of using TreatAs, couldn't we just create a relationship between Unrelated locations and fTAble like this ?"

That's a good point. Yes, that would work. You still need a measure to remove the filters on the location table. Something along the lines of:

Unrel. Sales = CALCULATE([Sum Sales], ALL('Location Table'))

(One reason not to  have a relationship is that it in a way keeps the model "simpler": if it's unrelated, you potentially avoid using the fields accidentally in visuals, slicers and filters (because they won't work directly), and in expressions using summarize, for example, intellisense won't let you reference fields from an unrelated table. Having said that, a direct relationship is more efficient than using TREATAS).

"And what is the point on using the first method with the hidden slicer, as the second method seems simplier ?  With the hidden slicer it only worked when I put a single field in my slicer. As soon as i put more than one field, the highlighting stopped to work."

No point really! I have used the second slicer in the past. Your question about multiple field selections got me thinking, and I came up with the method posted in this thread (which is a much better solution!). So you helped me explore a better option! Thank you!

[Aburar_123]

Hello @Bastienlin_ ,

You can keep using the dimension that is linked with the Fact table. Along with this you need to create an independant table that should have only the Slicer Values.

[Bastienlin_]

Hello @Aburar_123, thank you for your answer.

I already tried to do the same method with the indepedent table, but it seems that the color is not changing when the slicer is from a table linked to the fact table... Could you please share an example ?

Thank you !

[Aburar_123]

Hi @Bastienlin_ ,

For example, You have a Product Dimesion that is linked with Fact table Orders.

Product table columns are Product Category, Product Name, Product ID.

Suppose you want to have a slicer on Product Category. so you need to create a separate independant table like Product_Category = values(Product[Product Category]).

Then in the Scatter chart you need to use the Product[Product Category] where you need to use the below measure for highlighting the selected Product Category as below,

Selected_Product_color = if(max(Product[Product Category])=max(Product_Category[Product Category]), "Yellow","Blue")

Please mark it as a solution if it solves your issue. Thanks.

[Bastienlin_]

Hello @Aburar_123, thank you for your answer.

Tes this method works only if the slicer has fields from the independant duplicate table, on my use case I already have a Slicer from my dimension table. I would like this slicer to change the color, and I don't want to create second slicer with the fields from my duplicate independant table just to change the color.

Do you see what I mean ? Or maybe I didn't understand something from your explanation.
Thank you for your help !