Add bubble to chart based on slicer value

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 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

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!