Search Keyword from another Table and retrieve values

[Raif8522]

Hi Everyone,

i was trying to write a Dax formula to search a Keyword from Table B in "Comments" column on Table A and retrieve a "Date Open" & "Date Closed" values.

i tried to use  =Calculate(Firstnonblank([TableA],Filter([TableA],Search("Reference","Comments">0),ProductID))) but after SEARCH commend, "Reference" column is not available.

Table A
Date Open	Date Closed	Incident ID	Product ID	Comments
01/11/2022	01/12/2022	212	2	*****20221101-212-Screen Error*****
10/09/2021	10/10/2022	212	2	*****20211009-212-NoPrint*****
01/01/2023		215	1	*****20230101-215-ResistorFailure*****
12/12/2022	14/12/2022	217	3	*****20221212-217-NoError******
01/07/2022	08/07/2022	212	2	*****20220701-212-Unknown*****

Table B
Date Open	Date Closed	Product ID	Reference
?	?	212	20221101-212-Screen Error
?	?	217	20221212-217-NoError
?	?	212	20220701-212-Unknown

 Anyone here can help me with the formula or if you think PowerQuery will be better?

Many Thanks in advance.

[BeaBF]

@Raif8522 Hi! 

So you want to search for a word from the Reference column of Table B in the Comments column of Table A then taking the respective Date Open and Closed?

BBF

[Raif8522]

Yes, that's correct. column "Comments" on Table B is a text field where **** represents texts. 

i am going to check your solution below. 

Much appreciated for your response.

Thansk

[BeaBF]

@Raif8522 I have done that in Power Query, look at the attached pbix.

If that's correct, accept my answer as a solution.

BBF

[Raif8522]

Sorry my bad, forgot to mention that *** are texts, basically it's comments from customers. But reference on the "comments" column Table A & text on the "Reference" column Table B are unique.

I used SEARCH function to find the Reference in Comments column on Table A and retrieve Date Open & Date Closed values into Table B, but wasn't successful. 😕

[BeaBF]

@Raif8522 ok, calculate a new column in Table B as follow:

Date Open Tab A = MINX (

FILTER ( 'Table A', SEARCH ( 'Table B'[Reference], 'Table A'[Comments], 1, 0 ) > 0 ),

'Table A'[Date Open]

)

Do the same for the other one. Does it work?

BBF

[Raif8522]

@BeaBF 

Thanks for the formula. 

I added to the table and it is "Working onit" for last 2 hrs.

i think my Data set is way to large for the formula.

i have over 2mil records on Table A 😕

but, many thanks again for your respond