Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
h_l
Post Patron
Post Patron

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

h_l_0-1617969836584.png

 

 

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.

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@h_l , Try a new column in Table A like

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@h_l , Try a new column in Table A like

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

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

@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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.