Forum Discussion
Return Found Value in another Table
I have a list of "Keywords" (1 column table) that I'm using to find matches in a separate table on a free text field. The two tables do not have a relationship.
I created the below column, which works to return a Yes/No if there was a match in the keyword table:
Keyword Search =
IF(
SUMX(Keywords,
FIND(
UPPER(Keywords[Keywords]),
UPPER(DataNew[Name])
,,0
)
) > 0,
"Yes",
"No"
)
Is there a way to edit this to return which value it matched with? Also, if there is a way to do all of this in a measure rather than a column?
Hi astano05 ,
Please try the following measure:
Keyword Search Return Name = VAR tab = ADDCOLUMNS ( CROSSJOIN ( VALUES ( DataNew[Name] ), VALUES ( Keywords[Keyword] ) ), "find", SEARCH ( [Keyword], [Name], 1, 0 ) ) RETURN MAXX ( FILTER ( tab, [find] > 0 ), [Keyword] )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
What would your reason be for needing a measure? Is the result impacted by user filter interactions?
If you want to return two or more values in a column or measure you need to concatenate them with an agreed separator. The pipe symbol ( | ) is preferred as it then allows you to use the PATH functions to retrieve the list elements.
the only reason I'd prefer a measure is because the dataset I'm using may be brought into our larger shared cloud-based dataset where I am unable to create new columns.
How would I incorporate the pipe into the measure? I'm looking for the "return if true" part of the if statement to return the item in the key word list that it matched.
Keyword Search = IF( SUMX(Keywords, FIND( UPPER(Keywords[Keywords]), UPPER(DataNew[Name]) ,,0 ) ) > 0, "Yes|" & DataNew[Name], "No" )or similar - may need another aggregation like MIN() etc.
