Trying to look up from another table based on array

[Larry_USPS]

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"

[FreemanZ]

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]

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

[FreemanZ]

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:

[Larry_USPS]

Thanks for hanging with me and helping.. I think I found my issue, I am doing this in PowerPivot, not PowerBI, this the ContainsString isnt recoginized. Is there a way to do this within PowerPivot DAX commands ?

Thanks again

Larry

[FreemanZ · Accepted solution]

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]

That Worked ! ... THANK YOU !! 🙏

[vojtechsima]

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]

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]

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