March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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")
@EnrichedUser , Try a new column in SalesOrder
if(isblank(countx(filter(ApplicationAudit,search(SalesOrder[SalesOrderID], ApplicationAudit[Message],,0) >0),ApplicationAudit[Message])), "No", "Yes")
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
@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")
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
84 | |
77 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |