Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
astano05
Helper III
Helper III

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?

1 ACCEPTED 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] )

 image.png

 

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.

View solution in original post

10 REPLIES 10
lbendlin
Super User
Super User

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"
)

 image.png

 

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] )

 image.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.