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" ...
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:

vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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