Multiple Filters for Columns with Multiple Items

[Nicholas_B]

I made a post yesterday about a similar issue and while the solution worked for my example, it did not work for my overall project. So I have made another example more similar to my actual project. 

I am having an issue creating filters for columns that have multiple items in them, my dashboard looks like this: 

 

Currently I have two working filters for department and office that are also able to filter together (i.e. can use both simulatenously to filter further). I am having trouble creating two more filters for columns with multiple items in them. I would like to have filters where in this example, I click on Wednesday and everyone who has Wednesday anywhere in their day preference would be filtered, the same with color.  I would also like these filters to work with each other and the two other filters that are functioning correctly.

I have tried splitting the columns by delimeter into separate columns, however this makes it so my filter would only be able to filter one of those new created columns. I have also tried splitting the columns by delimeter into rows, but that leaves me multiple entries for the same line item. 

Is this possible to do in another way?

[v-yadongf-msft]

Hi @Nicholas_B ,

This is my test table:

Duplicate two tables ( used to create slicers):

Remove other columns and rename for them:

Split columns. Enter "," & space, because I noticed that there are spaces in your column.

Remove duplicates

You will get a table like this:

Same steps for the other table:

M code for your reference:

let
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Party Day Preference", type text}, {"Decoration Color Preference", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Party Day Preference"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Other Columns", {{"Party Day Preference", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Party Day Preference"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Party Day Preference", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type2")
in
    #"Removed Duplicates"

Close and apply to desktop.

Create two measures:

Week = IF(CONTAINSSTRINGEXACT(MAX('Table'[Party Day Preference]),SELECTEDVALUE('Party Day Preference'[Party Day Preference])),1,0)

Color = IF(CONTAINSSTRINGEXACT(MAX('Table'[Decoration Color Preference]),SELECTEDVALUE('Decoration Color Preference'[Decoration Color Preference])),1,0)

Add measures to table visual filter pane:

I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.