Remove elements from View that is selected in the Slicer

[Anonymous]

Hello, 

    In my report page, I have couple of views that shows relation between Countries and other values and different Slicers including one for the Country. The country slicer updates al lviews for that selected country only.

   I also have a clustered column chart that shows Volume as value of all countries in the dataset. Dataset is dynamic; countries may vary.

   I want a slicer for Countries especially for this visual. If the user selects any country of this Slicer, that country should not be visible in the chart/visual. 

   I can't get how do I filter to not show the selected ones - totally reverse of how we use Slicer/Filter.

   Any clue How can I accomplish this ? 

   Any help is appreciated.

Thanks

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

In my case , we could change the measure as follows:

Inverse Selector = 
IF(NOT(ISFILTERED(Slicer[Country])),1,IF(MAX([Country])  IN DISTINCT('Slicer'[Country]),0,1))

The final output is shown below:

In your case, you could try :

Inverse Selector =
VAR __CurrentCountry =
    MAX ( Merged_VOL_OMD[OMD.MAIN COUNTRY(ENG/BILLING/VENDORS/FINANCIALS)] )
VAR __Countries =
    DISTINCT ( RemoveCountries[Country] )
RETURN
    IF (
        NOT ( ISFILTERED ( Slicer[Country] ) ),1,
        IF ( __CurrentCountry IN __Countries, BLANK (), 1 ))

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Wow @v-yalanwu-msft . This works like a charm. 

Thanks a lot for your support, I highly appreciate it. 

[asitm]

Hi @Anonymous 

unless the visual you need is a complex one, you can simply use a custom visual. will save a lot of effort putting down complex formulas (though that's part of the fun sometimes)

I dont know the exact visual you are looking for (your screenshot suggests column chart though), but see if this custom visual is directionally how you need this, and I can help get you the right visual. when you click on any of the legend items, that will not show on visual. for e.g. clicking on 'Backing score' legend, will hide the 'Backing score' marks, and only show the 'Client Score' marks.

https://community.powerbi.com/t5/Data-Stories-Gallery/inverse-filter/m-p/1993630#M5621

Our company has developed a tool - PBIVizEdit - to create custom visual easily. no coding required. takes 15 minutes maybe. this visual was created with that tool. link:

https://pbivizedit.com/gallery/dual_axis_scatter

if this interests you, you can try it yourself. if you need any additional help, let me know.

[Anonymous]

Thanks @asitm , this is really cool, but it's not what I am looking for.

I am using bar charts showing Volumes (or different datas) of different countries. My requirement is, on selecting a country from the slicer, that country data should not appear in the chart. When no country is selected, all countries data should be shown. 

Check the visuals shown in previous posts.

[asitm]

Hi @Anonymous 

I had read the previous posts. Put this out in case there was an option to use legend based filtering instead of slicer.

But this is interesting. We haven't got to slicers yet. But when we do, we will see if the results can be implemented in slicers as well.

[v-yalanwu-msft]

Hi, @Anonymous ;

According to your screenshots and your measure ,I find one of the columns is inconsistent.

so  you could modify the measure as follows:

Inverse Selector =
VAR __CurrentCountry =
    MAX ( Merged_VOL_OMD[OMD.MAIN COUNTRY(ENG/BILLING/VENDORS/FINANCIALS)] ) 
VAR __Countries =
    DISTINCT ( RemoveCountries[Country] ) 
RETURN
    IF ( __CurrentCountry IN __Countries, BLANK (), 1 )

Attached is a simple example created by myself for your reference.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-yalanwu-msft , that was a good catch. Thanks for the solution demo file. 

I want is,

* If nothing is selected in the slicer, all the data should be shown. - So, if no country is selected, all 3 countries should appear.

* Else, not to show the selected items in the slicer. - If "Spain" is selected in the Slicer, "Spain" should not be shown, the other 2 must be shown.

Our solution right now takes care of the "Else" part, how can we manage the "If" part. 

[Greg_Deckler]

@Anonymous So like this? Inverse Selector - Microsoft Power BI Community

[Anonymous]

Yes @Greg_Deckler , exactly like that. When "AB" is selected, it shows everything other than "AB".

I think I should implement:

1. Store the unique countries OR the list that is shown in the Slicer as a Measure

Countries = DISTINCT(TableNm[Country])     // Replication for your Products

2:

Inverse Selector = 
VAR __dept = MAX([Department])   // What should be HERE ??  
VAR __countries = VALUES(Countries[Country])
VAR __table = SELECTCOLUMNS(FILTER(ALL('TableNm'),[Country] IN __Countries),"__dept",[Department])
RETURN
IF(__dept IN __table,BLANK(),1)

I can't get the following:

1. MAX of a table - what does that produce ? I can't find it in the docs - DAX - MAX, MAXA & MAXX Functions with an example - Power BI Docs

2. So this would return a seperate dataset/table named "Inverse Selector" ?

3. I would need "Real Volume (MB)" column that is shown as Values in the chart & "PMN" column as I show the Count of PMN in tooltip.

4. Importantly, I don't get how we will have the countries that are selected in the Slicer

It would be great if you can help me with this and understand it.

Thank You

[Greg_Deckler]

@Anonymous I'd have to download the PBIX with that quick measure to remember, I think I was using a disconnected table for product? So, in your case, you are going to always be referring to country but depends on the table. That actually simplifies things. Let's say you have your original table TableNm and your Countries table.

Inverse Selector = 
  VAR __CurrentCountry = MAX('TableNm'[Country]) //get the current country in context
  VAR __Countries = DISTINCT('Countries'[Country]) //get the countries selected in slicer
RETURN
  IF(__CurrentCountry IN __Countries, BLANK(), 1)

Now, this returns a measure that is either BLANK if the country is selected in the slicer or 1 if it is not. So, you filter your visual to this meausre = 1

[Anonymous]

Hello @Greg_Deckler , 

    I don't get the expected result; result is I either show all data or no data. I am sharing my tables and contents for better understanding:

Main Table                                                            Supporting Table

    

OMD.Main Country - as the Country & Real Volume as the Volume used in the visuals.

RemoveCountries - has all Distinct values of OMD.Main Country

Filters on my visuals:

Country, Partners, & Volume filters are locked - so they shouldn't have any impact on this visual. Only active filter is "Inverse Selector".

Slicer is :

To check, I created a measure "CountryChanged" that shows the selected country:

CountryChanged = SELECTEDVALUE(RemoveCountries[Country], 0)

However, I want MULTIPLE countries to be selected, so this won't work. It was just a test.

Inverse Selector:

Inverse Selector = 
    VAR __CurrentCountry = MAX(Merged_URC_OMD[OMD.MAIN COUNTRY(ENG/BILLING/VENDORS/FINANCIALS)]) // Get the current country in context
    VAR __Countries = DISTINCT(RemoveCountries[Country]) // Get countries selected in slicer
RETURN
    IF(__CurrentCountry IN __Countries, BLANK(), 1)

If nothing is selected in the slicer, I see blank chart:

To remove "Mexico", I select Mexico from the Slicer:

We see all the data including Mexico (the 1st country)

Can't get the error. Can you look and help me.

Thank You

[Greg_Deckler]

@Anonymous Would need your sample data to fully understand how to apply the pattern to your situation. 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.