Forum Discussion

Larry_USPS's avatar
Larry_USPS
New Member
1 year ago
Solved

Trying to look up from another table based on array

I have a collumn with paragraphs (words) in one table and in a 2nd table I have a list of words in a collumn.  I am trying to find a formula whereby DAX will look at all the words in the "paragraph" collumn and see if any of the words match the list of words in the other table.  Is this possable ?

 

Example:

Table 1[solution]: This solution will be found on page 246 of the manual

Table 2[words]:

Solution

fact

licence

 

The formula would return either a "Yes" or a count of 1 because it maych the word "Solution"

 

 

 

Need Help
  • FreemanZ's avatar
    FreemanZ
    1 year ago

    hi Larry_USPS ,

     

    try with SEARCH instead like:

    match2 = 
IF(
    COUNTROWS(
        FILTER(
            table2,
            SEARCH(table2[words], Table1[paragraphs], 1, 0)>0
        )
    )>0,
"Yes", "No"
)

     

    SEARCH shall work in Power Pivot and i verified the code in Power BI:

9 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    1 year ago

    Hello, Larry_USPS ,

    sure thing,

    given we have 1 table for paragraphs,  second for words:

     

    times_found = 
var currentWord = SELECTEDVALUE(words[words])
var containsWord = COUNTX(par, IF(CONTAINSSTRING(par[p], currentWord), 1))

return containsWord
    is_found = 
var currentWord = SELECTEDVALUE(words[words])
var containsWord = COUNTX(par, IF(CONTAINSSTRING(par[p], currentWord), 1))
var is_found = containsWord >= 1

return is_found
    • Larry_USPS's avatar
      Larry_USPS
      New Member
      1 year ago

      Thank you... can I invert this, so the match is on the table with the paragraphs?  The use case is trying to confirm people are using the assigned list of words in their submissions.  thanks again

      • vojtechsima's avatar
        vojtechsima
        Super User
        1 year ago

        Yeah, Larry_USPS ,

        no problem:

        is_found = 
var currentParagraph = SELECTEDVALUE(par[p])
var containsWord = COUNTX(words, IF(CONTAINSSTRING(currentParagraph, words[words] ), 1))
var is_found = containsWord >= 1

return is_found
        times_found = 
var currentParagraph = SELECTEDVALUE(par[p])
var containsWord = COUNTX(words, IF(CONTAINSSTRING(currentParagraph, words[words] ), 1))

return containsWord
  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi Larry_USPS ,

     

    try to write a calculated column in table1 like:

    match =

    IF(

    COUNTROWS(

        FILTER(

             table2,

             CONTAINSSTRING(table1[paragraphs], table2[words])

    )>0,

    "Yes", "No"

    )

    • Larry_USPS's avatar
      Larry_USPS
      New Member
      1 year ago

      Thanks Again, got an arror with the command "containsstring".  Tried Contains(Strings) that didnt work either.  THoughts?  thanks again

       

      • FreemanZ's avatar
        FreemanZ
        Super User
        1 year ago

        hi Larry_USPS ,

         

        indeed there are some typo, please try this:

        match = 
IF(
    COUNTROWS(
        FILTER(
            table2,
            CONTAINSSTRING(Table1[paragraphs], table2[words])
        )
    )>0,
"Yes", "No"
)

         

        it worked like: