topic Re: Confirm match - X-Lookup of multiple columns from different tables in DAX Commands and Tips

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

khisla — Tue, 25 Feb 2025 07:19:04 GMT

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!!

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

Irwan — Thu, 27 Feb 2025 03:47:20 GMT

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.

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

AntrikshSharma — Thu, 27 Feb 2025 06:49:29 GMT

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

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

khisla — Thu, 27 Feb 2025 09:44:54 GMT

Everything came out 0. There are no 1's

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

khisla — Thu, 27 Feb 2025 09:51:00 GMT

Got a syntax errror

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

AntrikshSharma — Thu, 27 Feb 2025 11:01:45 GMT

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

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

khisla — Thu, 27 Feb 2025 11:35:11 GMT

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.