Add bubble to chart based on slicer value

[Anonymous]

I am working with sales data, and I'm using a bubble chart visualization. The data spans two years. I'm plotting market share on the x-axis, year-over-year growth on the y-axis, and sizing the bubble by final year revenue. I filter the chart to the top 10 vendors by revenue. As I change my filters using slicers on country, product, etc., the bubble chart updates. All this works fine.
Sometimes, though, I'm asked to compare a vendor who is not in the top 10 with those that are. To do this, I've added a slicer for vendor and set it to require a single value. Ideally, I would add this to the bubble chart with the existing filter context. If the selected vendor is already in the top 10, great. Otherwise, I want to add them. If I could make their bubble a different color, that would be a bonus.

My thought is to create a table of top 10 vendors using the TOPN function, append the slicer value, and then use the vendor field as my filter. It's basically what I'd do in Python - create a list, append a value, and then test for membership in the list. In DAX, though, the process isn't as clear to me. My thinking goes like this:

PlotData =
VAR top10Vendors = TOPN(10, Sales, [Revenue])
VAR vendorList = VALUES(top10Vendors[Vendor])
VAR selectedVendor = SELECTEDVALUE(Sales[Vendor])
VAR tableData = FILTER(Sales, Sales[Vendor] IN vendorList || Sales[Vendor] = selectedVendor)
RETURN
   tableData

And then I'd use the dynamically created tableData table for my plot. However, I get an error saying:

Cannot find table 'top10Vendors'

Apparently my idea of using a temp table doesn't work. 

How should I approach this calculation?

Many thanks in advance.

[OwenAuger · Accepted solution]

Hi @Anonymous 

Interesting problem 🙂

I have attached a dummy PBIX containing one possible method.

First of all, I recommend that your data model includes both a Vendor dimension table, and a 'Vendor Selection' table. The 'Vendor Selection' table is a copy that is used for filtering the Vendors to be displayed in addition to the top 10. It has an inactive relationship with the Vendor table, which will be activated when requried via DAX.

Second, we need to create a means of filtering on the Top 10 Vendors, plus whatever is selected in the Vendor Selection table. This is along the lines of the code you posted above.

I actually tried two different methods:

1. Create a Calculation Group called Vendor Filter (using Tabular Editor) containing a Calculation Item "Top 10 plus Vendor Selction", to filter any measure to the Top 10 + Selected Vendors. Then apply this Calculation Item as a visual level filter on the bubble chart.
2. Create a measure Vendor Display Flag that returns 1 if the current Vendor is in the Top 10 + Selected Vendors. Then apply this measure as a visual level filter on the bubble chart, set equal to 1.

Code for these two methods is shown below:

1. Calculation Item

// CALCULATION ITEM
VAR VendorsTop10 = 
    CALCULATETABLE ( 
        TOPN ( 10,  VALUES ( Vendor[Vendor] ), [Revenue] ),
        ALLSELECTED ( Vendor ) -- Top 10 within any other Vendor filters
    )

-- Determine if Vendor Selection filter has been applied
VAR VendorSelectionFiltered = 
    ISFILTERED ( 'Vendor Selection'[Vendor Selection] )

-- Turn Vendor Selection into corresponding values from Vendor table
-- But filter to empty table if VendorSelectionFiltered = FALSE
VAR VendorSelection = 
    CALCULATETABLE (
        FILTER ( VALUES ( Vendor[Vendor] ), VendorSelectionFiltered ),
        ALL ( Vendor ),
        USERELATIONSHIP ( Vendor[Vendor], 'Vendor Selection'[Vendor Selection] )
    )

-- Union Top 10 & Selction (no need to remove duplicates since this will be applied as a filter)
VAR VendorsFinal = 
    UNION ( VendorsTop10, VendorSelection )
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        KEEPFILTERS ( VendorsFinal )
    )

2. Measure Vendor Display Flag

Vendor Display Flag = 
VAR VendorsTop10 = 
    CALCULATETABLE ( 
        TOPN ( 10,  VALUES ( Vendor[Vendor] ), [Revenue] ),
        ALLSELECTED ( Vendor ) -- Top 10 within any other Vendor filters
    )

-- Determine if Vendor Selection filter has been applied
VAR VendorSelectionFiltered = 
    ISFILTERED ( 'Vendor Selection'[Vendor Selection] )

-- Turn Vendor Selection into corresponding values from Vendor table
VAR VendorSelection = 
    CALCULATETABLE (
        FILTER ( VALUES ( Vendor[Vendor] ), VendorSelectionFiltered ),
        ALL ( Vendor ),
        USERELATIONSHIP ( Vendor[Vendor], 'Vendor Selection'[Vendor Selection] )
    )

-- Union Top 10 & Selction (no need to remove duplicates since this will be applied as a filter)
VAR VendorsFinal = 
    UNION ( VendorsTop10, VendorSelection )
RETURN
    -- 0/1 flag
    INT (
        CALCULATE (
            NOT ISEMPTY ( Vendor ),
            KEEPFILTERS ( VendorsFinal )
        )
    )

Lastly, I created a measure that returns 1 if the currently filtered Vendor is within the Vendor Selection filter. This measure is used to conditionally format the bubbles, if equal to 1.

Vendor Selection Flag = 
// Returns 1 if current Vendor filter contains Vendor Selection
VAR VendorSelectionFiltered = 
    ISFILTERED ( 'Vendor Selection'[Vendor Selection] ) -- determine if filter has been applied
VAR VendorSelection = 
    CALCULATETABLE (
        FILTER ( VALUES ( Vendor[Vendor] ), VendorSelectionFiltered ),
        USERELATIONSHIP ( Vendor[Vendor], 'Vendor Selection'[Vendor Selection] )
        -- ALL ( Vendor ) is not required here, as we want to intersect this with existing Vendor[Vendor] filter
    )
RETURN
    INT ( NOT ISEMPTY ( VendorSelection ) )

The end result then looks like this:

Well that's one method anyway. You could do essentially the same thing with 'Vendor Selection' being a disconnected table, with some adjustments to the DAX, but I generally prefer to use relationships where possible.

Are you able to apply/adapt this to your model?

Regards,

Owen

[OwenAuger · Accepted solution]

@Anonymous you're welcome!

Can you confirm that the relationship between the Vendor & 'Vendor Selection' tables is inactive?

Go to model view and the relationship should appear as a dotted line between those two tables.

If it's not, double-click the relationship arrow and untick "Make this relationship active" (or click Manage Relationships and untick the Active column next to the relationship).

If the relationship is active, making a selection on that slicer would indeed filter the visual as you have described, which is not what we want.

Let me know whether that fixes it 🙂

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @Anonymous 

Interesting problem 🙂

I have attached a dummy PBIX containing one possible method.

First of all, I recommend that your data model includes both a Vendor dimension table, and a 'Vendor Selection' table. The 'Vendor Selection' table is a copy that is used for filtering the Vendors to be displayed in addition to the top 10. It has an inactive relationship with the Vendor table, which will be activated when requried via DAX.

Second, we need to create a means of filtering on the Top 10 Vendors, plus whatever is selected in the Vendor Selection table. This is along the lines of the code you posted above.

I actually tried two different methods:

1. Create a Calculation Group called Vendor Filter (using Tabular Editor) containing a Calculation Item "Top 10 plus Vendor Selction", to filter any measure to the Top 10 + Selected Vendors. Then apply this Calculation Item as a visual level filter on the bubble chart.
2. Create a measure Vendor Display Flag that returns 1 if the current Vendor is in the Top 10 + Selected Vendors. Then apply this measure as a visual level filter on the bubble chart, set equal to 1.

Code for these two methods is shown below:

1. Calculation Item

// CALCULATION ITEM
VAR VendorsTop10 = 
    CALCULATETABLE ( 
        TOPN ( 10,  VALUES ( Vendor[Vendor] ), [Revenue] ),
        ALLSELECTED ( Vendor ) -- Top 10 within any other Vendor filters
    )

-- Determine if Vendor Selection filter has been applied
VAR VendorSelectionFiltered = 
    ISFILTERED ( 'Vendor Selection'[Vendor Selection] )

-- Turn Vendor Selection into corresponding values from Vendor table
-- But filter to empty table if VendorSelectionFiltered = FALSE
VAR VendorSelection = 
    CALCULATETABLE (
        FILTER ( VALUES ( Vendor[Vendor] ), VendorSelectionFiltered ),
        ALL ( Vendor ),
        USERELATIONSHIP ( Vendor[Vendor], 'Vendor Selection'[Vendor Selection] )
    )

-- Union Top 10 & Selction (no need to remove duplicates since this will be applied as a filter)
VAR VendorsFinal = 
    UNION ( VendorsTop10, VendorSelection )
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        KEEPFILTERS ( VendorsFinal )
    )

2. Measure Vendor Display Flag

Vendor Display Flag = 
VAR VendorsTop10 = 
    CALCULATETABLE ( 
        TOPN ( 10,  VALUES ( Vendor[Vendor] ), [Revenue] ),
        ALLSELECTED ( Vendor ) -- Top 10 within any other Vendor filters
    )

-- Determine if Vendor Selection filter has been applied
VAR VendorSelectionFiltered = 
    ISFILTERED ( 'Vendor Selection'[Vendor Selection] )

-- Turn Vendor Selection into corresponding values from Vendor table
VAR VendorSelection = 
    CALCULATETABLE (
        FILTER ( VALUES ( Vendor[Vendor] ), VendorSelectionFiltered ),
        ALL ( Vendor ),
        USERELATIONSHIP ( Vendor[Vendor], 'Vendor Selection'[Vendor Selection] )
    )

-- Union Top 10 & Selction (no need to remove duplicates since this will be applied as a filter)
VAR VendorsFinal = 
    UNION ( VendorsTop10, VendorSelection )
RETURN
    -- 0/1 flag
    INT (
        CALCULATE (
            NOT ISEMPTY ( Vendor ),
            KEEPFILTERS ( VendorsFinal )
        )
    )

Lastly, I created a measure that returns 1 if the currently filtered Vendor is within the Vendor Selection filter. This measure is used to conditionally format the bubbles, if equal to 1.

Vendor Selection Flag = 
// Returns 1 if current Vendor filter contains Vendor Selection
VAR VendorSelectionFiltered = 
    ISFILTERED ( 'Vendor Selection'[Vendor Selection] ) -- determine if filter has been applied
VAR VendorSelection = 
    CALCULATETABLE (
        FILTER ( VALUES ( Vendor[Vendor] ), VendorSelectionFiltered ),
        USERELATIONSHIP ( Vendor[Vendor], 'Vendor Selection'[Vendor Selection] )
        -- ALL ( Vendor ) is not required here, as we want to intersect this with existing Vendor[Vendor] filter
    )
RETURN
    INT ( NOT ISEMPTY ( VendorSelection ) )

The end result then looks like this:

Well that's one method anyway. You could do essentially the same thing with 'Vendor Selection' being a disconnected table, with some adjustments to the DAX, but I generally prefer to use relationships where possible.

Are you able to apply/adapt this to your model?

Regards,

Owen

[Anonymous]

@OwenAuger Thanks again for the file and detailed explanation. I've been implementing the Display Flag version with my production data, but it's not working yet. When I click on a vendor in the Vendor Selection slicer, it keeps filtering to only that one vendor. I'm using your Vendor Display Flag and Vendor Selection Flag code exactly, and I have the dimension tables for Vendor Selection and Vendor linked just as in your diagram. I've applied the Vendor Display Flag to the bubble chart visual and set it to 1. However, when I select a vendor from the Vendor Selection slicer, the chart changes to a single bubble for the selected vendor. I'll keep looking, since I'm clearly doing something wrong.
Thanks again for this! I wasn't sure if it would even be possible.

[OwenAuger · Accepted solution]

@Anonymous you're welcome!

Can you confirm that the relationship between the Vendor & 'Vendor Selection' tables is inactive?

Go to model view and the relationship should appear as a dotted line between those two tables.

If it's not, double-click the relationship arrow and untick "Make this relationship active" (or click Manage Relationships and untick the Active column next to the relationship).

If the relationship is active, making a selection on that slicer would indeed filter the visual as you have described, which is not what we want.

Let me know whether that fixes it 🙂

Regards,

Owen

[Anonymous]

@OwenAuger Last question, I swear. Where do you set the color of the bubble for the selected vendor to red? I've combed the code and formatting options, but I can't find that trick anywhere. (You're quite the magician) 😲

[OwenAuger]

Glad the relationship settings fixed the earlier problem 🙂

On the conditional formatting for the colour of the bubbles, actually I kind of glossed over that.

Here are the steps:

1. Select the bubble chart
2. Go to the Visualizations Pane > Format Pane
3. Under Data colors under Default Color click the fx
4. Change the settings to
   • Format by: Rules
   • Based on field: Vendor Selection Flag
   • If value: is 1 Number then red (or whatever your prefer)

For me the color defaulted to blue when the rule condition wasn't met (which I think is theme-dependent), but you could add another rule to handle that as well.

Hope that all works for you at your end!

Kind regards,

Owen 🙂

[Anonymous]

That's odd. In the .pbix file you sent me, I see the fx option under data colors.

In my file, I do not.

It's the same visual, so there must be a configuration setting somewhere. When I look at the file options, the only difference I can find is that yours regional settings are English (New Zealand). 
I'm running the latest production release:

Hmmm.

[OwenAuger · Accepted solution]

Ah right - it looks like you have included Year as a Legend field on the visual - is that correct?.

Remove it, and you should then have conditional formatting available.

Including a Legend field means colour is set corresponding to the values of that field, and isn't compatible with conditional formatting.

Hopefully that works!

[Anonymous]

@OwenAuger It is now perfect. Thank you so much for this!

[Anonymous]

D'oh! I totally missed the dashed lines in your original solution (along with the text saying "It has an inactive relationship with the Vendor table, which will be activated when requried via DAX."
Yes indeed, that fixed it! Thanks again. This is really cool.

[Anonymous]

@OwenAuger Thank you sir! This looks to be just what I'm trying to do. I'll give it a go in the morning and report back.