Confirm match - X-Lookup of multiple columns from different tables

[khisla]

Hello BI Community

I have a DAX question.

I have 2 tables and am looking to confirm if the combination of 2 columns (MerchantOrderId + SKU) from Table 1 noting customer orders exists within Table 2 (order_no + item_id) noting customer returns.

Example Tables:

Table 1 - Customer Orders

OrderId	DateCreated	MerchantOrderId	SKU
GE5241740127IL	1/1/2024	AIL00726997	IF3904_550
GE5241740127IL	1/1/2024	AIL00725280	GZ0024_610
GE5243332964IL	1/1/2024	AIL00728139	HQ4099_320
GE5243332964IL	1/1/2024	AIL00728139	EE5599_660
GE5243332964IL	1/1/2024	AIL00725281	HB1437_270

Table 2 - Customer Returns

date_order_created_omni	order_no	status_desc	return_date	item_id
1/1/2024	AIL00725280	Return Completed	1/21/2024 3:44	GZ0024_610
1/1/2024	AIL00725281	Return Completed	1/10/2024 21:46	HH8510_250
1/1/2024	AIL00725281	Return Completed	1/10/2024 21:46	IC8296_270
1/1/2024	AIL00725281	Return Completed	1/10/2024 21:46	HB1437_270

Expected Result-

Within Table 1 - Customer Order Table a new column confirming the match

OrderId	DateCreated	MerchantOrderId	SKU	Match
GE5241740127IL	1/1/2024	AIL00726997	IF3904_550	0
GE5241740127IL	1/1/2024	AIL00725280	GZ0024_610	1
GE5243332964IL	1/1/2024	AIL00728139	HQ4099_320	0
GE5243332964IL	1/1/2024	AIL00728139	EE5599_660	0
GE5243332964IL	1/1/2024	AIL00725281	HB1437_270	1

Thanks in advance!!

[AntrikshSharma]

@khisla 

Match = 
VAR Lookup =
    LOOKUPVALUE (
        'Customer Returns'[item_id],
        'Customer Returns'[item_id], 'Customer Orders'[SKU],
        'Customer Returns'[order_no], 'Customer Orders'[MerchantOrderId]
    )
VAR Result =
    INT ( NOT ( ISBLANK ( Lookup ) ) )
RETURN
    Result

[khisla]

Everything came out 0.  There are no 1's

[AntrikshSharma]

@khisla I used the file created by @Irwan and it works for me.

[Irwan · Accepted solution]

hello @khisla 

please check if this accomodate your need.

create new calculated column in 'Customer Orders' table with following DAX

Match =
var _Match =
MAXX(
    FILTER(
        'Customer Returns',
        'Customer Returns'[order_no]='Customer Orders'[MerchantOrderId]
    ),
    'Customer Returns'[date_order_created_omni]
)
Return
IF(
    ISBLANK(_Match),
    0,
    1
)

Hope this will help.

Thank you.

[khisla]

I am getting items with muliple matches because it does not take into consideration the SKU combined with the Order ID.  Should be 1 to 1.  

[khisla]

Got a syntax errror