Forum Discussion
Solved
Count multiple strings in a column
Hello, I have a column which can have multiple users; it looks like: Column1 Row 1: UserA Row 2: UserB Row 3: UserB, UserC, UserG, UserZ Row 4: User D, Row 5: UserA, UserD, Use...
Hello @pal95 ,
You will need to split your column into multiple columns in the query editor as shown in this blog:
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
You can then count the occurrences of a string in multiple columns that reference the following existing thread:
https://community.powerbi.com/t5/Desktop/Count-instances-of-string-in-multiple-columns/td-p/239302
Thank you
Pragati
Hi pal95,
You can try to use following calculate table formulae to analysis raw table and generated the summarize user tables based on each item and their count:
Summary =
VAR merge =
SUBSTITUTE (
CONCATENATEX ( ALL ( 'Table'[Column1] ), [Column1], "," ),
" ",
""
)
VAR _path =
SUBSTITUTE ( merge, ",", "|" )
VAR list =
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( _path ), 1 ),
"Users", PATHITEM ( _path, [Value] )
)
RETURN
FILTER (
GROUPBY ( list, [Users], "Count", COUNTX ( CURRENTGROUP (), [Users] ) ),
[Users] <> BLANK ()
)
Regards,
Xiaoxin Sheng