topic Re: Search Keyword from another Table and retrieve values in DAX Commands and Tips

Search Keyword from another Table and retrieve values

Raif8522 — Mon, 16 Jan 2023 10:45:46 GMT

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.

Re: Search Keyword from another Table and retrieve values

BeaBF — Mon, 16 Jan 2023 11:19:46 GMT

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

Re: Search Keyword from another Table and retrieve values

BeaBF — Mon, 16 Jan 2023 11:32:10 GMT

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

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

BBF

Re: Search Keyword from another Table and retrieve values

Raif8522 — Mon, 16 Jan 2023 11:48:20 GMT

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

Re: Search Keyword from another Table and retrieve values

Raif8522 — Mon, 16 Jan 2023 11:54:15 GMT

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. 😕

Re: Search Keyword from another Table and retrieve values

BeaBF — Mon, 16 Jan 2023 13:48:51 GMT

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

Re: Search Keyword from another Table and retrieve values

Raif8522 — Mon, 16 Jan 2023 14:25:11 GMT

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