Need to create a slicer that filters on a column sometimes containing multiple values

[Anonymous]

Hi all,

First, I am using PowerPivot in Excel 2016, I am not sure of feature crossover between PP and Power BI but am hoping someone here can provide guidance.

Essentially, I have a Power Pivot table structured in the following way:

Name	Location
Name1, Name2	a
Name 1	b
Name 2	c
Name2	d
Name1, Name2	e

Which is to say that some locations can have both names at them. I want to be able to filter the names by location. However, if I add Name as a slicer, I get a slicer menu that looks like this:

Name1

Name2

Name1,Name2

Theoretically, if I want to isolate all locations with name1, even if they also have name2, I just need to select Name1 and Name1,Name2 from the slicer menu. However, this is confusing my users because there are about 20 unique names they want to filter on, and tons of combinations of those names. What I really need is a slicer menu that looks like this:

Name1

Name2

And when they select Name1, all the locations including Name1 and Name1,Name2 are filtered on. I hope that this explanation makes sense, as well as the use case for this filter. I'm at a dead end and any help would be very much appreciated.

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

>>Theoretically, if I want to isolate all locations with name1, even if they also have name2, I just need to select Name1 and Name1,Name2 from the slicer menu.

Unfortunately, slicer and filter not support this.
You can consider to create a table with items, then you can write a measure to compare selected value and current row content and use it on visual level filter.

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks so much for your reply. This is kind of where I had wound up - I was able to create a visual-level filter by making a separate table with the values I wanted and using DAX to compare the selected value from that table with the pivot table, then hiding non-matches. I suppose the solution here is just clearly explaining to the users how to use the slicer menu.

Thanks again!