Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Search text in another table and return value

Hi there,

 

I know there has been a lot posts about this topic, but I've tried a lot of provided solutions, not working, so I create another post.

Want to create a new column in TableA, if a value in [Page] Endswith value in TableB, then return the value in TableB.

 

 

Sample Data in TableA

PageClicksImpressionsCTRPosition
https://www.domain.com/gb/zh/docs/dc506487160.24.0
https://www.domain.com/es/zh/docs/dc704054190.17.1
https://www.domain.com/bd/ja/docs/dc778849120.518.0
https://www.domain.com/us/en/vdos/vd503308-precision-touchpad-settings140.31.5
https://www.domain.com/us/en/vdos/vd503308160.22.2
https://www.domain.com/us/zh/docs/dc777577120.51.0
https://www.domain.com/us/zh/docs/dc777577-how-to-enable-auto-drive120.51.0
https://www.domain.com/es/zh/docs/dc500749130.32.0
https://www.domain.com/us/en/docs/dc704077140.36.8
https://www.domain.com/us/en/docs/dc704077-enable-light-detect120.52.5
https://www.domain.com/us/en/docs/1297327835283120.52.5
https://www.domain.com/us/en/docs/migra-2214120.52.5
https://www.domain.com/us/en/docs/sport_z1_presenter120.52.5
https://www.domain.com/us/en/docs/z1120.52.5
https://www.domain.com/us/en/docs/z1-auto-detect120.52.5

 

TableB:

Vanity ID
/12957349875069381
/migra-2214
/sport_z1_presenter
/z1

 

Expected result

 

 

I tried use DAX but seems all returns "FALSE". Tried to use M as well, but as this post mentioned, have no idea why it was loading over 41GB without ending.....still loading....

 

 

Thanks in advance for your inputs.

Have a good day.

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try a new column in Table A like

    maxx(filter(TableB, right(TableA[Page],len(TableB[Vanity ID])) = Table[Vanity ID]), Table[Vanity ID])

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a new column in Table A like

    maxx(filter(TableB, right(TableA[Page],len(TableB[Vanity ID])) = Table[Vanity ID]), Table[Vanity ID])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandakThanks for reply, however, after I applying the DAX, Power BI keeps in "Working..." for hours...until now, still "Working...", the symptom looks really like the "Loading...." issue in my another post.

       

      I assume it's because some specific value is existing in TableB, such as:

      /b80de788-69b7-4104-b9e0-e08620201476

      But it's weird, coz I've set the column in TableB as "text", imo, no matter what value inside, shall not impact how Power BI working...

      I am doing test...

       

       

      By the way, if I would like to just check if a value in [Page] contains (not Endswith) value in TableB, then return the value in TableB, could you please advise a new DAX?

      Thanks again!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      After hours running, the result comes out. THANK YOU! amitchandak