Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
DAX : Pivot Text into a List of Words - @Phil_Seamark
DAX – “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2
For further details and explanation check the blog post below (Note: blog is in Portuguese but google translate works pretty good).
eyJrIjoiMDJkMzlmYmUtMDNkNy00NTJhLThhOGEtZDJhYTc4NGYzYTY0IiwidCI6IjczNGQyYTI3LThjODktNDMyOC1iZTYwLTRhMWQxMWNkNWM3OCIsImMiOjl9
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix I just realized I re-invented the wheel on Pivot Table to List of Words like a year later with Text to Table. I really should pay more attention to @Phil_Seamark's stuff...
Don't think like that you are giving alternative to the persons you are making a wheel with a different material 😁.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português