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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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