01-15-2021 10:22 AM - last edited 01-15-2021 10:28 AM
Based on a request on the community this little piece of code allows to select a sentance and mark all the sentances that have any of the words in their syntax.
Find Word Formula = // Character that split phrase into words VAR SplitByCharacter = " " // Temporary table that splits selected phrase into words VAR Words_table = FILTER ( ADDCOLUMNS ( GENERATE ( SELECTCOLUMNS ( ALLSELECTED ( 'Table A'[Description] ), "Find_Text", 'Table A'[Description] ), VAR TokenCount = PATHLENGTH ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ) ) RETURN GENERATESERIES ( 1, TokenCount ) ), "Word", PATHITEM ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ), [Value] ) ), //Filter of words not to be searched in text NOT ( [Word] IN VALUES ( 'Don''t filter words'[Word] ) ) ) RETURN IF ( // Function that returns the sum of the values of the words found in the sentance SUMX ( Words_table, FIND ( // additonal spaces in the beginning and ending of each word allows to find the exact match, also removed the last "." so that would be specific words " " & UPPER ( SUBSTITUTE ( [Word], ".", "" ) ) & " ", " " & UPPER ( SUBSTITUTE ( SELECTEDVALUE ( 'Table B'[Title] ), ".", "" ) ) & " ", , 0 ) ) > 0, "Found Word", "No Words Match" )
The setup includes:
This code is based on the merge of two solutions for finding words and making a sentance split:
For further details and explanation check the blog post below (Note: blog is in Portuguese but google translate works pretty good).