Forum Discussion
Search Keyword from another Table and retrieve values
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.
6 Replies
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. 😕
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
