Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
Solved! Go to 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 ) )
)
)
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 ) )
)
)
Awesome - Works perfectly well!
Thank you
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |