Highlighting Scatter Charts in Power BI using DAX

March 16, 2018

Another Friday, another tip. Power BI has undergone a lot of innovative updates over the years, but my favorite one has been a combination of the cross-filtering & highlighting  capabilities, multi-selecting across visuals and drilling down filtering other visuals. I feel having those capabilities are so important to make the flow of data exploration and analysis intuitive for end users, and I had written a long post back in 2015 where I compared Power BI to Tableau, the undisputed leader in data exploration at that time. Fast forward another 2-2.5 years, and there has been so much progress that Power BI is the new leader and other tools like Tableau & Qlik are trying to catch-up (in fact, we are at this junction where I could write a similar post on how it is easier to do data analysis in Power BI compared to Tableau – try checking out the number of clicks it would take to implement drill-downs, highlighting/filtering, etc. compared to Power BI or just the very basic task of putting 6 charts in one page for analysis). That said, there is one feature from my previous blog that was not implemented in Power BI – highlighting scatter/bubble charts. In Power BI, the scatter charts are not considered as area charts and hence you can only filter them and not highlight. This feature is useful when you have a lot of data points in your scatter chart and you want to see where a particular data point is with respect to the other data points. That said, you can make use of some nifty DAX and replicate the same behavior.

Header

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.

image

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.

1 Original behavior

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.

image

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.

image

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.

2 Highlighting behavior

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

Posted by SQLJason

11 comments

Thanks

Frank Tonsen

Brilliant approach!
But do we need the SUMX ?

Sel_Diabetes =
VAR Diab =
SUM ( Diabetes[Number] )
RETURN
SWITCH (
SELECTEDVALUE ( IsSelectedFlag[Flag] );
“Y”; [DiabetesSlicer];
“N”; IF ( Diab [DiabetesSlicer]; Diab )
)

I thought about using this formula initially. Problem is – let’s say that this same measure is displayed in a table/matrix, the grand total would be blank with your formula. This will work for a scatter chart, but I wanted it to work across any chart if possible (for e.g., putting this measure in a Card visual to show the total).

Frank Tonsen

Thanks, with other visuals this makes sense of course. Very professional!

I came here to learn how to make a scatter graph highlight instead of filter.
I didn’t learn anything because you showed 3 different things and I don’t understand how one affect the other.

Why include the report tooltip and filtering of Diabetes Count By State in a guide “highlighting scatter charts in power bi using dax”?

Answer to “Why include” – because I thought it would be useful and it is my blog. Sorry you did not feel that way.

Answer to “I didn’t learn anything because I have added 3 different things” – The only thing extra is the Report tooltip. You need the filtering of Diabetes count by state to see the highlighted points in scatter chart. Unless there is a second visual, how can you highlight something in the scatter chart?

Sorry mate, I vented out on you.
I am crossed with PowerBi at this stage as simple thing like this is giving me a headache where in excel it would take me 10 minutes.
I’ve wasted a lot of time trying to do what my manager asked me to do – to highlight a value on scatter graph depending on what is selected on a slicer.

I managed to write a measure that does it, but you cannot put measure on “Legend” in the scatter graph. I tried to approach it from different angles but I don’t know much about DAX or PowerBI as I never had a proper training on it.

Which is exactly the scenario that I explained in this article. Note that I am using a calculated column called Flag in the Legend. That is the one which determines whether the values are highlighted or not. Since that field is a disconnected table, I am making sure that the measure links the Flag. Please download the pbix file and see if that is what you want.

Yes, this is why I got angry because I don’t understand how that works.
It’s hard to understand for somebody who’s PowerBI amateur, and you made it even more difficult to understand by having additional measures and tables that do not serve the purpose of explaining “Highlighting Scatter Charts”.
Or how does DiabetesSlicer work? What does INTERSECT function does and why do you wrap your columns with VALUES function?

Once again, all those steps are required (except the report tooltip). I would advise you to start learning the basics instead of getting angry at someone providing a free service, or you could have even asked me or others nicely how things work. If you think some of the steps like additional measures and tables are unnecessary, that is because you didn’t understand the concept. The measures are commented for a reason, but if you don’t take the time and effort to read and understand, there is little others can do. Also, search engines can help when you want to know what functions like INTERSECT or VALUES do.

How does the selected state in bar chart link to the scatter?
I see no sentence about this.

Leave a Reply