Forum Discussion

astano05's avatar
astano05
Icon for Helper III rankHelper III
5 years ago
Solved

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 ...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

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.

  • astano05's avatar
    astano05
    Icon for Helper III rankHelper III
    5 years ago

    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.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      5 years ago

       

      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.

       

      • astano05's avatar
        astano05
        Icon for Helper III rankHelper III
        5 years ago

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