Highlighting Scatter Charts in Power BI using DAX

 

First, let me show you the existing behavior in Power BI. For demonstrating that, I have a dataset that has the count of people with diabetes as well as the population by State in the US.

I made a simple bar chart on the left that shows the diabetes count by state, and another scatter chart that shows the Diabetes % (population adjusted value) and the population by states (special thanks to the folks at PowerBI.tips for the lovely layouts). Notice how the dots in the scatter chart get filtered when I click on the bar chart.

Now, follow the steps below so that you can replicate the highlighting functionality:-

1) Our intention is to create a cross-filtering effect for the State in the bubble chart. For that, we will have to create a disconnected table for State (say StateSlicer) and also create another table called IsSelectedFlag that has just 2 values – Y and N. Create two calculated tables with the formula below:-

StateSlicer = State

IsSelectedFlag = UNION ( ROW ( “Flag”, “Y” ), ROW ( “Flag”, “N” ) )

2) Create a measure called DiabetesSlicer that will display the diabetes count for the States in the disconnected table.

DiabetesSlicer =
CALCULATE (
    SUM ( Diabetes[Number] ),
    INTERSECT ( VALUES ( State[State] ), VALUES ( StateSlicer[State] ) )
)

You can now make a bar chart from the StateSlicer and the DiabetesSlicer.

3) The next step is to make a scatter chart for Diabetes% (which is just the diabetes count / population for the state) and Population by State. The important thing here is to make a measure for the diabetes count that will show both the selected and unselected values. The Flag can be used as a legend for that purpose, but before it can work, we first need to write some DAX as shown below.

Sel_Diabetes =
— Diabetes count
VAR Diab =
    SUM ( Diabetes[Number] )
RETURN
    SUMX (
        –iterating for each value of the flag
        VALUES ( IsSelectedFlag[Flag] ),
        — calculating the selected value of the flag       
        VAR SelVal =
            CALCULATE ( SELECTEDVALUE ( IsSelectedFlag[Flag] ) )
        RETURN
            SWITCH (
                SelVal,
                –if it is Yes (meaning only the selected values), display DiabetesSlicer
                — DiabetesSlicer shows value only for selected state in bar chart               
                “Y”, [DiabetesSlicer],
                — if no (for values not selected), subtract from the total       
                — for unselected values, DiabetesSlicer is blank, but Diab is always displayed        
                “N”, IF ( Diab <> [DiabetesSlicer], Diab – [DiabetesSlicer] )
            )
    )

It is advised to use the same technique for Population also, especially if you are having other charts. But in this scenario, where I only have a bar chart and scatter chart, I can get away with just changing one of the measures of the scatter chart (which is the Diabetes% in this case). Create the Diabetes% measure now

Sel_Diabetes% =
DIVIDE ( [Sel_Diabetes], SUM ( Population[Population] ) )

4) Now create a scatter chart with the State from the original state table, flag in the legend and the Population and Sel_Diabetes% as the Axes.

5) Now you should be able to see the highlighting functionality. I also added some slicers and the metrics in the left hand side, as well as a simple report tooltip (just because I LOVE this feature). Also, change the default colors for the Legend, ideally choose a dark color for Yes and a lighter shade of the same color for No so that it looks natural.

Feel free to download the pbix file from here and play with it.