Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |