Forum Discussion

Nicholas_B's avatar
Nicholas_B
New Member
3 years ago

Multiple Filters for Columns with Multiple Items

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?

Need Help

1 Reply

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    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.