cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
badger123 Member
Member

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.

 

Capture.PNG

The way I did this was by creating another table:

Table3 = SUMMARIZE(Table1, Table1[Item], Table1[Term ID]
 
Capture.PNG
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: 
Capture.PNG
 
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. 
 
Capture.PNG
 
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: 
5 REPLIES 5
Highlighted
Super User
Super User

Re: Slicer to exclude

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
badger123 Member
Member

Re: Slicer to exclude

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?

 

Capture.PNG

Super User
Super User

Re: Slicer to exclude

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.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
badger123 Member
Member

Re: Slicer to exclude

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! 

 

Capture.PNG

Super User
Super User

Re: Slicer to exclude

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.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 6 members 1,242 guests
Please welcome our newest community members: