Slicer setting for 'Contains' rather than equals?

[jbaich]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Teams[/TD]
[/TR]
[TR]
[TD]Team 1[/TD]
[/TR]
[TR]
[TD]Team 2[/TD]
[/TR]
[TR]
[TD]Team 3[/TD]
[/TR]
</tbody>[/TABLE]


Task Table
[TABLE="width: 500"]
<tbody>[TR]
[TD]Task Type[/TD]
[TD]Team Responsible[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD]Team 1[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]Team 1 or Team 3[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]Team 2[/TD]
[/TR]
</tbody>[/TABLE]

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]

Google for “disconnected slicers”

 

[macfuller]

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]

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]

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]

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]

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]

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]

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]

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

Whoops, just seen you already did.