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:

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

     

    • Larry_USPS's avatar
      Larry_USPS
      New Member
      1 year ago

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