Forum Discussion
Find string matches from a separate table
Hi folks,
I'm hoping to get some assistance. I need to create a report that takes a list of strings ("Application" table) as an input, counts the number of text strings separated by spaces, and scans through a separate table ("Ticket" table) and counts how many times each text string separated by spaces appears.
Here's a sample 'Application' table:
| Name |
| 01db |
| 12d Model |
| 12d Reference |
| 1a Ref 1d 1c |
| ODBC |
| Network Support |
Here is a sample 'Ticket' table:
| Description |
| 1a is in here |
| Issue with Network |
| Referencing Model |
| 1c1a |
| Primary model |
| Issue with ODBC, ODBC is offline |
And here is a sample 'Results' table that demonstrates what I'm after. I'm looking to create a virtual table that lists each Application Name, gives me a column that counts each word in the Application Name, and then counts each occurrence of each word as it appears in the entire 'Ticket' table (only complete matches, and not row-by-row matches - a match could come from any row):
| Name | String Count of Name | Hits of each 'Name' word in any 'Description' cell |
| 01db | 1 | 0, |
| 12d Model | 2 | 0, 2 |
| 12d Reference | 2 | 0, 0 |
| 1a Ref 1d 1c | 4 | 1, 0, 0, 0 |
| ODBC | 1 | 2, |
| Network Support | 2 | 1, 0 |
Any help would be greatly appreciated 🙂
MichaelHutchens why you have partial code, my code is way too long, not sure if you are not seeing the full code:
Word Found Count = VAR __word = SUBSTITUTE ( 'Application'[Name], " ", "|" ) VAR __wordTable = GENERATESERIES ( 1, PATHLENGTH ( __word ) ) VAR __wordsTable = ADDCOLUMNS ( __wordTable, "@Word", PATHITEM ( __word, [Value] ) ) VAR __add1 = ADDCOLUMNS ( __wordsTable, "Found Count", SUMX ( 'Description', VAR __desc = SUBSTITUTE ( 'Description'[Description], " ", "|" ) VAR __descTable = GENERATESERIES ( 1, PATHLENGTH ( __desc ) ) VAR __descTableWord = ADDCOLUMNS ( __descTable, "@Description", PATHITEM ( __desc, [Value] ) ) VAR __descValues = SELECTCOLUMNS ( __descTableWord, "@Description", [@Description] ) RETURN ( [@Word] IN __descValues ) + 0 ) ) RETURN CONCATENATEX ( __add1, [Found Count], "," )Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
6 Replies
MichaelHutchens add the following columns in your application table to get the result, you can easily convert it into measures (if you want)
Word Count = VAR __word = SUBSTITUTE ( 'Application'[Name], " ", "|" ) RETURN PATHLENGTH( __word ) Word Found Count = VAR __word = SUBSTITUTE ( 'Application'[Name], " ", "|" ) VAR __wordTable = GENERATESERIES ( 1, PATHLENGTH ( __word ) ) VAR __wordsTable = ADDCOLUMNS ( __wordTable, "@Word", PATHITEM ( __word, [Value] ) ) VAR __add1 = ADDCOLUMNS ( __wordsTable, "Found Count", SUMX ( 'Description', VAR __desc = SUBSTITUTE ( 'Description'[Description], " ", "|" ) VAR __descTable = GENERATESERIES ( 1, PATHLENGTH ( __desc ) ) VAR __descTableWord = ADDCOLUMNS ( __descTable, "@Description", PATHITEM ( __desc, [Value] ) ) VAR __descValues = SELECTCOLUMNS ( __descTableWord, "@Description", [@Description] ) RETURN ( [@Word] IN __descValues ) + 0 ) ) RETURN CONCATENATEX ( __add1, [Found Count], "," )and here is the output
Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thanks so much for the quick response parry2k 🙂 I'm getting the error below when attempting to create the second column - what am I doing wrong?
MichaelHutchens can you copy the full expression code rather than a screenshot, seems like it is missing some parenthesis, cannot figure it out from the partial image, share the code.
Sure parry2k , here you go:
Word Found Count =
VAR __word = SUBSTITUTE ( 'Application'[Name], " ", "|" )
VAR __wordTable = GENERATESERIES ( 1, PATHLENGTH ( __word ) )
VAR __wordsTable = ADDCOLUMNS ( __wordTable, "@Word", PATHITEM ( __word, [Value] ) )
MichaelHutchens why you have partial code, my code is way too long, not sure if you are not seeing the full code:
Word Found Count = VAR __word = SUBSTITUTE ( 'Application'[Name], " ", "|" ) VAR __wordTable = GENERATESERIES ( 1, PATHLENGTH ( __word ) ) VAR __wordsTable = ADDCOLUMNS ( __wordTable, "@Word", PATHITEM ( __word, [Value] ) ) VAR __add1 = ADDCOLUMNS ( __wordsTable, "Found Count", SUMX ( 'Description', VAR __desc = SUBSTITUTE ( 'Description'[Description], " ", "|" ) VAR __descTable = GENERATESERIES ( 1, PATHLENGTH ( __desc ) ) VAR __descTableWord = ADDCOLUMNS ( __descTable, "@Description", PATHITEM ( __desc, [Value] ) ) VAR __descValues = SELECTCOLUMNS ( __descTableWord, "@Description", [@Description] ) RETURN ( [@Word] IN __descValues ) + 0 ) ) RETURN CONCATENATEX ( __add1, [Found Count], "," )Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Apologies, my fault completely parry2k 🙂 Your original response works perfectly - thanks so much, I really appreciate it! 🙂
