Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
---|---|
68 | |
47 | |
21 | |
19 | |
15 |
User | Count |
---|---|
123 | |
42 | |
39 | |
26 | |
24 |