How to create a filter that extract unique data from a list of comma separated data in a column

[aleki]

Need your help on the following use-case.

I've got a table named "Sales" with a column named "Customers"

Each row contains a list of customers separated by a comma - Here is an example

I need to create a filter in PowerBI that will only show each customer name

If I use my "Customers" column as a source of my filter, I'll get all the unique list of customers.

What I need if a filter that allows me to select "Cx1 and filter all row that contains "Cx1" - That would be

[tamerj1 · Accepted solution]

Hi @aleki 
Please refer to attached sample file with the proposed solution

Place the filter measure that best suits your requirement in the filter pane of the table visual, select "is not blank" then apply the filter.

Customer = -- DAX to generate the unique list of customers to be used in the slicer
DISTINCT ( 
    SELECTCOLUMNS ( 
        GENERATE ( 
            ALLNOBLANKROW ( Sales[Customers] ),
            VAR String = Sales[Customers]
            VAR Items = COALESCE ( SUBSTITUTE ( String, ",", "|" ), 1 )
            VAR Length = PATHLENGTH ( Items )
            RETURN 
                SELECTCOLUMNS ( 
                    GENERATESERIES ( 1, Length, 1 ),
                    "@Item", PATHITEM ( Items, [Value] )
                )
        ),
        "Customer", [@Item]
    )
)

Filter Any of Selected = 
COUNTROWS ( 
    FILTER ( 
        Sales,
        VAR String = Sales[Customers]
        VAR Items = COALESCE ( SUBSTITUTE ( String, ",", "|" ), 1 )
        VAR Length = PATHLENGTH ( Items )
        VAR T =  
            SELECTCOLUMNS ( 
                GENERATESERIES ( 1, Length, 1 ),
                "@Item", PATHITEM ( Items, [Value] )
            )
        RETURN
            NOT ISEMPTY ( INTERSECT ( VALUES ( Customer[Customer] ), T ) )
    )
)

Filter All of Selected = 
COUNTROWS ( 
    FILTER ( 
        Sales,
        VAR String = Sales[Customers]
        VAR Items = COALESCE ( SUBSTITUTE ( String, ",", "|" ), 1 )
        VAR Length = PATHLENGTH ( Items )
        VAR T =  
            SELECTCOLUMNS ( 
                GENERATESERIES ( 1, Length, 1 ),
                "@Item", PATHITEM ( Items, [Value] )
            )
        RETURN
            ISEMPTY ( EXCEPT ( VALUES ( Customer[Customer] ), T ) )
    )
)

[aleki]

Awesome - Works perfectly well!

Thank you