Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |