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

Teams

Team 1

Team 2

Team 3

<tbody>
</tbody>

Task Table

Task Type	Team Responsible
01	Team 1
02	Team 1 or Team 3
03	Team 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]

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]

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]

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?

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]

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]

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]

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