Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
aleki
Microsoft Employee
Microsoft Employee

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

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

aleki_0-1688892099476.png

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

aleki_1-1688892413293.png

 

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

aleki_2-1688892618806.png

 

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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.

1.png2.png3.png

 

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 ) )
    )
)

 

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

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.

1.png2.png3.png

 

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
Microsoft Employee
Microsoft Employee

Awesome - Works perfectly well!

Thank you

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.