Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
What I'm looking to return is the Keywords field from the find funciton, not the Name field. I tried this, but i got the error that "A table of multiple values was supplied where a single value was expected."
Keyword Search Return Name =
IF(
SUMX(Keywords,
FIND(
UPPER(Keywords[Keyword]),
UPPER(DataNew[Name])
,,0
)
) > 0,
values(Keywords[Keyword]),
"No"
)
Keyword Search Return Name =
IF(
SUMX(Keywords,
FIND(
UPPER(Keywords[Keyword]),
UPPER(DataNew[Name])
,,0
)
) > 0,
CONCATENATEX(values(Keywords[Keyword]),"|"),
BLANK()
)
Do you see that you can simplify this measure if that's what you want to return? I think right now it will return all keywords, not just the matched ones.
If you like to provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) I can show you a better option.
This allows the formula to run, but just returns "||||" for all the matched fields. It doesnt return which value in the keyword list that it matched. I think the issue may be that the Find function just returns a number rather than the actual value that matched
Hi @astano05 ,
Please try the following measure:
Keyword Search Return Name =
LOOKUPVALUE (
Keywords[Keyword],
Keywords[Keyword], MAX ( DataNew[Name] ),
"No"
)
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,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This doesn't work. The lookup that I need is approximate. I am trying to replicate a fuzzy lookup. The below formula I have works to give a yes/no for an approximate look up to a list, but I want it to return which value matched instead of "Yes"
IF(
SUMX(ContractorKeywords,
FIND(
UPPER(ContractorKeywords[Contractor Keyword]),
UPPER(DataNew[CONTRACTOR])
,,0
)
) > 0,
"Yes",
"No"
)
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,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This works perfectly! thank you
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |