Forum Discussion

AuroraNI's avatar
AuroraNI
Helper III
6 years ago
Solved

Highlight single point on scatter plot using slicer instead of filtering

Hi,

I would like a slicer to highlight or conditionally format the data point in a scatter plot when you select a value in a slicer.  I attach a photo below.  I would like to choose a single name in the slicer on the right and the corresponding data points be conditionally coloured red in the scatter plot.  If not possible I would be happy for the data points to be highlighted.  I have looked at some solutions online but have been unable to figure out.

Thanks

  • AuroraNI 

    I think this is what you are after:

     

    The method I followed was:
    1) In power query, create a "Highlight ID" in the main table (basically concatenate [name] & [Time (secs)] & [x with jitter])

    2) In power query, create a Dimension Table (for your slicer) including [name] and [Highlight ID]

     

    3) load into the model. Make sure there are no relationships between the fact table and the newly created Dim table.

     

     

    4) Set up the slicer using the [Name] field from the newly created 'Dim Table Name'

    5) Set up the scatter chart as you have done with the "x with jitter" and "Time (secs)" as your axes (make sure the fields are not summarized or aggregated). Both these fields are obviously from the main fact table.

    6) Create you conditional formatting measure, like:

     

     

     

     

    Condit Format Highlight measure = 
    VAR check = VALUES('Dim Table Name'[Highlight ID])
    VAR table_= VALUES('scatter chart highlight'[Highlight ID])
    VAR calc = COUNTROWS(
       INTERSECT(check, table_))
    Return
    IF(calc =1, 2)

     

     

     

     

    5) Use this measure as your "Rule field" in the conditional formatting inputs (set the colour for a true of "2"

    And that's it.

    PS. You might find some points are not visible. This is because of the density of the data (some points may be "hidden" behind others). You might have to play around with the bubble size option, the fill point or the actual colours

     

    Here is the PBIX file for your reference:

10 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion

    AuroraNI 

    I think this is what you are after:

     

    The method I followed was:
    1) In power query, create a "Highlight ID" in the main table (basically concatenate [name] & [Time (secs)] & [x with jitter])

    2) In power query, create a Dimension Table (for your slicer) including [name] and [Highlight ID]

     

    3) load into the model. Make sure there are no relationships between the fact table and the newly created Dim table.

     

     

    4) Set up the slicer using the [Name] field from the newly created 'Dim Table Name'

    5) Set up the scatter chart as you have done with the "x with jitter" and "Time (secs)" as your axes (make sure the fields are not summarized or aggregated). Both these fields are obviously from the main fact table.

    6) Create you conditional formatting measure, like:

     

     

     

     

    Condit Format Highlight measure = 
    VAR check = VALUES('Dim Table Name'[Highlight ID])
    VAR table_= VALUES('scatter chart highlight'[Highlight ID])
    VAR calc = COUNTROWS(
       INTERSECT(check, table_))
    Return
    IF(calc =1, 2)

     

     

     

     

    5) Use this measure as your "Rule field" in the conditional formatting inputs (set the colour for a true of "2"

    And that's it.

    PS. You might find some points are not visible. This is because of the density of the data (some points may be "hidden" behind others). You might have to play around with the bubble size option, the fill point or the actual colours

     

    Here is the PBIX file for your reference:

    • AuroraNI's avatar
      AuroraNI
      Helper III

      PaulDBrown Thank you so much that is terrific and thank you also for the detailed explanation it made it very easy to understand the steps you took!  Have a great day!

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion

        AuroraNI 

        Hi again. Just wanted to point out that the method works even if you use the [Name] fields in the conditional formatting measure (no need for the setting up Highlight IDs etc). The reason I went with the Highlight IDs was because I was going to use SELECTEDVALUE (until I realised that each name had more than one point in the chart.)

        Anyway, Just thought it was worth mentioning.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Change the slicer to a table. 

    now when you click on a name, it should dynamically highlight the corresponding data. 

    • AuroraNI's avatar
      AuroraNI
      Helper III

      Hi Thanks for the reply,

      I have tried this but it filters the data and only shows the selected data points...I would like to keep all the other data points either in the same original colour or faded.

  • kman42's avatar
    kman42
    Frequent Visitor

    I am trying to get this to work with my scatter plot, but I don't see any conditional formatting options. I have Position on the X-Axis, Count on the Y-Axis, and Name in the Legend. I then turned all of the individual legen markers to be the same color, but I don't see a conditional formatting option of a color function for the markers. Any help?

     

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion

      In the original example, the conditional formatting is applied to the marker itself. So under Marker -> Color