Forum Discussion
Helper IIIMeasure: Find Partial Text in Unrelated Table
Hi,
I have two unreleated tables with 1 common field.
My goal is to create a measure that can identify, for any given SalesOrderID, if there exists a partial match for "Order added to warehouse" in another table's "Message" column.
Table1: ApplicationAudit
| BranchID | ApplicationID | TransactionNumber | Message | ModAt | AutoID |
| SF01 | SALESORDER | 1118778 | Order added to warehouse order 00035991. | 12/30/2020 8:51 | 17898821 |
| SF01 | SALESORDER | 1056768 | Order added to warehouse order 00036431. | 1/8/2021 14:18 | 18141984 |
| SF01 | SALESORDER | 1118781 | Order added to warehouse order 00037859. | 2/4/2021 14:40 | 18944137 |
| DADF | SALESORDER | 1118628 | Order added to warehouse order 00036097. | 1/4/2021 10:14 | 17958991 |
| DADF | SALESORDER | 1118628 | To Bin: SHIP01 | 1/4/2021 13:32 | 17970590 |
Table2:SalesOrder
| BranchID | SalesOrderID |
| RK10 | 1123590 |
| DADF | 1123552 |
| SF01 | 1056768 |
| SF01 | 1118778 |
| DADF | 1118628 |
Expected Output (messure):
| BranchID | SalesOrderID | Added to warehouse |
| RK10 | 1123590 | NO |
| DADF | 1123552 | NO |
| SF01 | 1056768 | YES |
| SF01 | 1118778 | YES |
| DADF | 1118628 | YES |
Notes:
Both tables have additional fields but ApplicationAudit.TransactionNumber = SalesOrder.SalesOrderID
SalesOrderIDs 1056768, 1118778 and 1118628 have "Yes" under "Added to warehouse" because in the applicationaduit table, the same TransacationNumber has a log under the message column for "Order added to warehouse"
SalesOrderID and TransactionNumber have duplicates in each table
EnrichedUser , Try some thing like
if(isblank(countx(filter(ApplicationAudit,SalesOrder[SalesOrderID] = ApplicationAudit[TransactionNumber] && search("Order added to warehouse", ApplicationAudit[Message],,0) >0),ApplicationAudit[Message])), "No", "Yes")
3 Replies
- amitchandak
Super User
EnrichedUser , Try a new column in SalesOrder
if(isblank(countx(filter(ApplicationAudit,search(SalesOrder[SalesOrderID], ApplicationAudit[Message],,0) >0),ApplicationAudit[Message])), "No", "Yes")
EnrichedUserHi amitchandak
Thank you for your quick reply. Unfortuntaly, this was not able to resolve.
looking at your dax:
if(isblank(countx(filter(ApplicationAudit,search(SalesOrder[SalesOrderID], ApplicationAudit[Message],,0) >0),ApplicationAudit[Message])), "No", "Yes")
it does not take into account the message field for "Order added to warehouse"Also, SalesOrderID would not be in Message but TranscationNumber
- amitchandak
EnrichedUser , Try some thing like
if(isblank(countx(filter(ApplicationAudit,SalesOrder[SalesOrderID] = ApplicationAudit[TransactionNumber] && search("Order added to warehouse", ApplicationAudit[Message],,0) >0),ApplicationAudit[Message])), "No", "Yes")