Slicer to exclude

[badger123]

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

Item	Term	Term ID	Country	Tag
Item one	this term is about item one and item two and mentions tag one	id1	1	tag one
Item one	this term is about item one only and mentions tag two	id2	1	tag two
Item one	another term about item one	id3	1
Item one	term about items one and three	id4	1
Item two	this term is about item one and item two and mentions tag one	id1	1	tag one
Item two	this term is about item two	id5	1
Item three	term about items one and three	id4	1
Item one	this term is about item one and item two	id6	2
Item one	this term is about item one only and mentions tag two	id7	2	tag two
Item two	this term is about item one and item two	id6	2

Table 2:

Term ID	Date	Values
id1	Jan-19	100
id2	Jan-19	300
id3	Jan-19	250
id4	Jan-19	900
id5	Jan-19	100
id6	Jan-19	50
id7	Jan-19	20
id1	Feb-19	90
id2	Feb-19	280
id3	Feb-19	100
id4	Feb-19	800
id5	Feb-19	50
id6	Feb-19	50
id7	Feb-19	10
id1	Mar-19	80
id2	Mar-19	200
id3	Mar-19	250
id4	Mar-19	850
id5	Mar-19	100
id6	Mar-19	60
id7	Mar-19	30

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: 

https://drive.google.com/file/d/1Kiv8bG9Uq7U-joustW48NqZAgDaP7HI2/view?usp=sharing

[MattAllington]

Have a look at my article here https://exceleratorbi.com.au/items-not-selected-slicer/

[badger123]

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]

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.

[badger123]

Thanks @MattAllington , although I haven't been able to get that to work. Sorry if my first message wasn't 100% clear. In my table, there is a list of terms which are basically tagged up accordingly. So a term can be tagged against multiple items for example. I want to use the items in a slicer to exclude the terms attached to the selected item. E.g. if item 1 is selected, this would exclude terms 3, 4 and 6, meaning any item 2 calculations would be done based on the remaining terms (1, 2 and 5). I've been struggling to find an efficient way of achieving this for weeks! 

[MattAllington]

All DAX formulas are specific to the table, columns and relationships in your model.  You don't mention how these things are structured so it is not possible to give you any more help.  Your image seems to suggest that each row in a table can have more than 1 tag, but it is not clear how that works.  Do you have many columns, 1 tag per column, or do you have 1 column with comma separators, or do you have a child tag table?  The latter is what I suggested.