Measure: Find Partial Text in Unrelated Table

[EnrichedUser]

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

Added to warehouse =

IF(ISBLANK(

MINX(

FILTER(ApplicationAudit, SEARCH("Order added",ApplicationAudit[Message],1,0) > 0 ),

SalesOrders[Lines])),

"YES",

"NO")

[amitchandak]

@EnrichedUser , Try a new column in SalesOrder

if(isblank(countx(filter(ApplicationAudit,search(SalesOrder[SalesOrderID], ApplicationAudit[Message],,0) >0),ApplicationAudit[Message])), "No", "Yes")

[EnrichedUser]

Hi @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 · Accepted solution]

@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")