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:

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