# Slicer setting for 'Contains' rather than equals?



## jbaich (Feb 21, 2018)

Hi all, I am wondering if/how i can modify either my slicer settings or my data in order to achieve a regular looking slicer that acts more like a filter for 'contains' rather than equals...

So i have a table with a list of my teams and another table with a list of tasks and which teams are responsible for them...

I'd like the slicer to display the list of Teams, but filter the task table by the "Team Responsible" column as though the filter were behaving as if the column "contained" the selected value from the slicer rather that was equal to that selection...

Team List Table

TeamsTeam 1Team 2Team 3

<tbody>

</tbody>

Task Table

Task TypeTeam Responsible01Team 102Team 1 or Team 303Team 2

<tbody>

</tbody>
 So for example I am trying to get the slicer to show the 3 Team values, not including a "Team 1 or Team 3" value. And if Team 1 is selected, the Pivot Table would filter to include both Task Types 01 and 02...

Is this possible either through some sort of slicer setting or via changing the Team Responsible data to some other format such as "Team 1; Team 3" whereby the slicer would recognize the cell holds 2 values rather than 1?

Also, I have these tables in my data model and they are linked to other tables, so just adding another row for Task Type 02 so that i have one row for each of Team 1 and Team 3 doesn't work as the Task Type is my primary key for my relationships with other tables...

I haven't had any luck experimenting with different cell value formats and can't seem to find anything on the internet that answers this question.

Thanks,
Joe


----------



## gazpage (Feb 21, 2018)

Google for “disconnected slicers”


----------



## macfuller (Feb 23, 2018)

Rob Collie had an explanation here.
https://powerpivotpro.com/2014/01/containsx-revisited-what-was-the-match/#more-8965

When you set up the slicer with your search values be aware (at least it worked this way when I set it up) that it will eliminate spaces.  So if you are searching for "rat " (with a space at the end) to get rodents the slicer will trim the spaces and return "congrats", "oratory", and other equivalents as well.  Not a problem if you have a limited number of search terms.


----------



## jbaich (Feb 25, 2018)

Hey guys, thanks for the responses! I'm still really new to powerpivot and DAX and keep getting stumped on things that sound like they should be really simple... can't seem to get my brain to stop thinking in the traditional excel construct.

So, I've been reading up on disconnected slicers and it seems there are several different strategies out there, some people say calculated columns are the way to go, some say just use measures... i'm not great with either, but columns seems more familiar with my current level of excel experience. I've tried a number of different things so far with no luck.

I've uploaded a sample workbook to dropbox https://www.dropbox.com/s/ntrh2ohnqq2pe88/SlicerTest.xlsx?dl=0 if anyone wants to take a peek... it's gotten slightly more complex since my original post in that now I'm trying to filter my table using the disconnected slicer against 2 columns. So in a nut shell, if the slicer values selected are HR and Sales, I'd like to filter the table so that any rows containing EITHER of these values in EITHER column are returned...

I'm using excel 2016, but dont seem to have the functions CONTAINSX or SELECTEDVALUE available, I have tried various combinations of IF(HASONEVALUE),VALUES... CONCANTENATEX, CONTAINS and a few others that I've read about, but so far no luck... I've tried passing the selected slicer values into variables for use in the DAX formulas, but clearly doing something wrong there as well...

As I said, very new at this... much of what I'm doing is basically attempting to reverse engineer other peoples examples that i'm reading about, but I just don't seem to have a broad enough grasp on how some of these new functions should work or work together.

If anyone would be able to help me get started even if that's just through a bit of a layman's explanation on the steps i need to take, i'd very much appreciate it.

Thanks,
Joe


----------



## gazpage (Feb 26, 2018)

Work your way up to it. 

First get it working for one column with the target hardcoded into the formula. Next replace the hard coding with the result of the disconnected slicer. Then add a second column as hardcoding. Finally replace that hardcoding.


----------



## jbaich (Feb 26, 2018)

That's essentially what I've been trying to do, but I'm just not getting something... so I thought at the most basic, I should be able to get 
	
	
	
	
	
	



```
TeamsSelected:=IF(HASONEVALUE(TeamsTable[Teams]),"T","F")
```
 to give me a T of F depending on if I've got one or more my Slicer values selected, but no matter what, it's always giving me "F"... I can see that the slicer is actively filtering the TeamsTable so I don't get why the measure isn't responding to give me a T when I've only selected a single value???

So, I thought I'd try another approach with 
	
	
	
	
	
	



```
Teams Selected(2):=CONCATENATEX(ALLSELECTED(TeamsTable[Teams]),TeamsTable[Teams],",")
```
Basically getting the same results in that it always returns all the values in my TeamsTable, regardless of how I slice it...

Am I referencing something wrong?


----------



## gazpage (Feb 26, 2018)

Your first formula works for me when I replicate it. If I had to guess I would say your slicer is not connected to the visualisation that you are putting the measure in.


----------



## jbaich (Feb 26, 2018)

I haven't really been concerned with controlling a visualization yet, i've just been focusing on trying to get the measure to return a value that makes sense...

I've also tried working through the example here https://eriksvensen.wordpress.com/2015/03/23/get-selected-items-in-a-slicer-in-excel-2016/

Which seems to be a simplified version of the previous CONCATENATEX approach: 
	
	
	
	
	
	



```
SelectedTeams:=CONCATENATEX(TeamTable,[Team],",")
```
I continued following that example and place this formula in a cell in my workbook 
	
	
	
	
	
	



```
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[SelectedTeams]",Slicer_Team)
```
, which as I understand it should show me the text values of the slicer values selected... but all i get is #NA ...

pretty stumped right now and a little frustrated 

Thanks,
Joe


----------



## gazpage (Feb 26, 2018)

Measures only return values in the context of a visualisation, even if that visualisation is just a pivot table or card. So now I am confused!

Where are you seeing the result of your measure? Can you share a dummy version of your work?


----------



## gazpage (Feb 26, 2018)

gazpage said:


> Where are you seeing the result of your measure? Can you share a dummy version of your work?



Whoops, just seen you already did.


----------



## jbaich (Feb 21, 2018)

Hi all, I am wondering if/how i can modify either my slicer settings or my data in order to achieve a regular looking slicer that acts more like a filter for 'contains' rather than equals...

So i have a table with a list of my teams and another table with a list of tasks and which teams are responsible for them...

I'd like the slicer to display the list of Teams, but filter the task table by the "Team Responsible" column as though the filter were behaving as if the column "contained" the selected value from the slicer rather that was equal to that selection...

Team List Table

TeamsTeam 1Team 2Team 3

<tbody>

</tbody>

Task Table

Task TypeTeam Responsible01Team 102Team 1 or Team 303Team 2

<tbody>

</tbody>
 So for example I am trying to get the slicer to show the 3 Team values, not including a "Team 1 or Team 3" value. And if Team 1 is selected, the Pivot Table would filter to include both Task Types 01 and 02...

Is this possible either through some sort of slicer setting or via changing the Team Responsible data to some other format such as "Team 1; Team 3" whereby the slicer would recognize the cell holds 2 values rather than 1?

Also, I have these tables in my data model and they are linked to other tables, so just adding another row for Task Type 02 so that i have one row for each of Team 1 and Team 3 doesn't work as the Task Type is my primary key for my relationships with other tables...

I haven't had any luck experimenting with different cell value formats and can't seem to find anything on the internet that answers this question.

Thanks,
Joe


----------



## jbaich (Feb 26, 2018)

much of the experimenting I've been doing has occurred since i uploaded that sample so it likely won't show my trials and errors... I thought that i would be able to see the results of my attempts in the actual measures themselves in the data model? so where I made the measure 
	
	
	
	
	
	



```
[COLOR=#333333]TeamsSelected:=IF(HASONEVALUE(TeamsTable[Teams]),"T","F")[/COLOR]
```
 it displays as TeamsSelected: F in the data model cell... i thought if i then selected only one value from the slicer it would display as TeamsSelected: T... no? Same idea with the CUBEVALUE... expected that formula to display dynamically as i made different selections in the slicer?


----------



## gazpage (Feb 26, 2018)

Ok, I downloaded your file and then did the following.

1. Go into the Data Model and changed TeamsSelected to Teams Selected:= IF(HASONEVALUE(TeamsTable[Teams]),"T","F")
2. Go into the right hand pivot table and drag out all of the fields so it is a blank table
3. Drag TeamSelected into the values.
4. Click various options on the slicer. When only one is selected I see a T, but if I select multiple I see an F. This is what I was expecting.


Maybe you can replicate these steps? Perhaps I just misunderstand what you are trying to do?


----------



## gazpage (Feb 26, 2018)

jbaich said:


> much of the experimenting I've been doing has occurred since i uploaded that sample so it likely won't show my trials and errors... I thought that i would be able to see the results of my attempts in the actual measures themselves in the data model? so where I made the measure
> 
> 
> 
> ...



Not really, no. The data model screen itself is fixed at refresh (i.e. when you load or refresh the data). It is not affected by any slicers or filters. Therefore that measure would always show an F in the data model (assuming the column has more than one value).


----------



## jbaich (Feb 26, 2018)

Ok!!! Thanks! so that worked and now the other CONCATENATEX does too, I was just expecting it to work a little differently, but i think i get it now kind of... 

So, using 
	
	
	
	
	
	



```
[COLOR=#333333]Teams Selected:=CONCATENATEX(ALLSELECTED(TeamsTable[Teams]),TeamsTable[Teams],","[/COLOR]
```
 returns the list of values selected in the slicer, which are the values I want to use to filter my fact table, but in a "contains" context...

So I've got 2 columns in my fact table that contain codes that correspond to one or more teams... That sounds complicated, in the actual table I only have codes, in my data model i've added columns that look up the team values from the lookup tables...

 I would like to filter the pivot table based on these columns and the selection of values now calculated in the Teams Selected measure above. My initial thought is that it would _sound_ something like "Filter(IF(OR(column2 contains any of [Teams Selected], column4 contains any of [Teams Selected])))"

I know that's not proper syntax, but i'm more just sounding it out to try and grasp what it is i'm after... so if my slicer has HR and Sales selected, my measure is returning "HR, Sales"... I want to filter columns 2 and 4 to return the rows that contain HR or Sales in either column... some cells may contain various combinations such as 'Sales/Finance'... since this cell contains sales, i would like it to return a True value or be included in the filtered result....

How do i tell it to look at my Teams Selected measure of 'HR, Sales' as two variables HR and Sales rather than one long string 'HR, Sales'? 
The CONTAINS function looks promising... 
	
	
	
	
	
	



```
SYNTAX: [COLOR=#000000][FONT=Consolas]CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…) [/FONT][/COLOR]
```

So I'm thinking something along the lines of 
	
	
	
	
	
	



```
[I]CROSS[/I](?)FILTER([COLOR=#000000][FONT=Consolas]CONTAINS(FactTable, [Team Responsible Task], [/FONT][/COLOR][I]Teams Selected measure[U](?)[/U][/I][COLOR=#000000][FONT=Consolas][I], [/I][/FONT][/COLOR][COLOR=#000000][FONT=Consolas][Team Responsible Department], [/FONT][/COLOR][I]Teams Selected measure[U](?)[/U][/I][COLOR=#000000][FONT=Consolas]) [/FONT][/COLOR]
```

SO, in the garbage line above, I'm not sure about
1. The Cross part... do i need that because I'm looking in 2 columns?
2. How to pass the Teams Selected measure as multiple 'OR' criteria
3. How to formulate so that it filters for matches of the passed criteria in column 2 OR column 4... if my slicer variables are HR and Sales, but column 2 is 'Finance' and column 4 value is 'Sales'... that should return as True
4. Am I totally going down the wrong path and should be looking at something more along the lines of 
	
	
	
	
	
	



```
Phase2Filter:=Filter(Contains(OR(FactTable,[Team Responsible Task],[Teams Selected]),(FactTable,[Team Responsible Dep.],[Teams Selected])))
```
 I know that doesn't work either, but hopefully it kind of speaks to what i'm trying to do and how i'm thinking i should be trying to solve it? or is this more of a C

Thanks,
Joe


----------



## gazpage (Feb 27, 2018)

What you are trying to do is not the easiest DAX formula I can think of. You want to create a fresh table with the list of all your teams, and have no relationships to the rest of your data model. Create the slicer against this table (the "Disconnected slicer"). Formula would then be something like:

    Measure:=
        CALCULATE (
            SUM ( Data[Sales] ),
            FILTER (
                Allselected ( Data[Teams] ),
                COUNTROWS (
                    FILTER (
                        Disconnected,
                        FIND ( Disconnected[Teams], Data[Teams],,-1) >= 0
                    )
                ) > 0
            )
        )

I am sure this won't work first time, but the principle is:

1. Iterate through each 'row' in the Teams column in your Data table.
2. For each 'row', then iterate though the disconnected slicer table (which will only be the items on via the slicer).
3. Use FIND() to check if the item on the disconnected row is included in the data row, return a -1 if not.
4. Keep rows in the disconnected table that had a match and discard the rest.
5. The resulting table is only the matches, so if COUNTROWS() returns a number > 0 then there was a match for at least one of the items on the disconnected table.
6. Discard the rows in the Data table where there was no match against the Disconnected table.
7. In my example SUM the Sales column based on these remaining rows.

Phew!


----------



## jbaich (Feb 28, 2018)

Thanks Gaz! I will chew on that today!

Have you ever had any difficulties with the drill through when you've got multiple slicer values selected and you try to double click a grand total or subtotal to load those records into a new sheet? I keep getting an error message saying that Excel can't do it because i've selected too many filters... is that just me or is that the same for everyone? I don't seem to recall ever having that problem with v 2007...

Thanks,
Joe


----------

