Forum Discussion

badger123's avatar
badger123
Icon for Resolver I rankResolver I
7 years ago

Slicer to exclude

Hello,

 

I'm trying to use a slicer to exclude things selected. Please bear with me on this why I try explain my problem using simplified tables. Table 1 and Table 2 are related by Term ID. I would like to create a slicer using Item whereby if items are selected, the corresponding terms are excluded. 

 

Table 1

ItemTermTerm IDCountryTag
Item onethis term is about item one and item two and mentions tag oneid11tag one
Item onethis term is about item one only and mentions tag twoid21tag two
Item oneanother term about item oneid31 
Item oneterm about items one and threeid41 
Item twothis term is about item one and item two and mentions tag oneid11tag one
Item twothis term is about item twoid51 
Item threeterm about items one and threeid41 
Item onethis term is about item one and item twoid62 
Item onethis term is about item one only and mentions tag twoid72tag two
Item twothis term is about item one and item twoid62 

 

Table 2:

Term IDDateValues
id1Jan-19100
id2Jan-19300
id3Jan-19250
id4Jan-19900
id5Jan-19100
id6Jan-1950
id7Jan-1920
id1Feb-1990
id2Feb-19280
id3Feb-19100
id4Feb-19800
id5Feb-1950
id6Feb-1950
id7Feb-1910
id1Mar-1980
id2Mar-19200
id3Mar-19250
id4Mar-19850
id5Mar-19100
id6Mar-1960
id7Mar-1930

 

E.g. if I selected Item one and country 1, then this is how I would expect my two table visuals to look. This is because all terms relating to item one have been excluded.

 

The way I did this was by creating another table:

Table3 = SUMMARIZE(Table1, Table1[Item], Table1[Term ID]
 
Then this measure:
Measure = 
VAR check = COUNT('Table3'[Term ID])
RETURN
IF(ISFILTERED(Table3[Item]),
IF(isblank(check),1,BLANK()),
1)
If I add the measure in as a column into both table visuals, this only works for the first table. To get it to work for both, I have to add a visual level filter using the Term ID from Table 1 like this: 
 
Yes this all works as expected but I'm wondering if there is a better way to achieve this? ,y approach seems convoluted and it feels like I'm cheating by adding a top N filter to all my visuals. It also gets more complicated for some of my other visuals, where I want to use Top N for other things, e.g. I have a ribbon chart (I love this visual) with Values from Table 2 and Tag from Table 1 as the legend. I would like the slicer to work in the same way, excluding the terms based on the item/s selected, but I also only want to show the Top 10 Tags based on the sum of Values. This is a poor example because I haven't got enough dummy data but here I would like to show the top 2 Tags in the ribbon chart based on the sum of Values (taking into account the terms that have been excluded) - so only the blank Tag and Tag two would be showing. 
 
 
I would appreciate any advice on this, specifically how to best achieve the exclude functionality and how I can create the ribbon chart showing the top N tags as well as excluding certain terms. 
 
Here is a link to the dummy file: 
Need Help

5 Replies

    • badger123's avatar
      badger123
      Icon for Resolver I rankResolver I
      7 years ago

      Thanks MattAllington  . That's a great article and I had already read it and tried that approach. Unfortunately it doesn't work for my use case because the terms are sometimes against multiple items - see below. Any ideas?

       

      • MattAllington's avatar
        MattAllington
        Icon for Community Champion rankCommunity Champion
        7 years ago

        I am not 100% sure what you are doing, but my guess is you need to extract all the values you want to filter on from the ext field into another table, and then unpivot them so they are in a single column.  This implies your text table needs an ID and the new table has the same ID and one row for every item (header/detail table structure).  You can then turn on bi-directional filtering to make it all work.  That is the approach I would try.