Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
khisla
Helper I
Helper I

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

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

 

OrderIdDateCreatedMerchantOrderIdSKU
GE5241740127IL1/1/2024AIL00726997IF3904_550
GE5241740127IL1/1/2024AIL00725280GZ0024_610
GE5243332964IL1/1/2024AIL00728139HQ4099_320
GE5243332964IL1/1/2024AIL00728139EE5599_660
GE5243332964IL1/1/2024AIL00725281HB1437_270

 

Table 2 - Customer Returns

 

date_order_created_omniorder_nostatus_descreturn_dateitem_id
1/1/2024AIL00725280Return Completed1/21/2024 3:44GZ0024_610
1/1/2024AIL00725281Return Completed1/10/2024 21:46HH8510_250
1/1/2024AIL00725281Return Completed1/10/2024 21:46IC8296_270
1/1/2024AIL00725281Return Completed1/10/2024 21:46HB1437_270

 

Expected Result-

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

 

OrderIdDateCreatedMerchantOrderIdSKUMatch
GE5241740127IL1/1/2024AIL00726997IF3904_5500
GE5241740127IL1/1/2024AIL00725280GZ0024_6101
GE5243332964IL1/1/2024AIL00728139HQ4099_3200
GE5243332964IL1/1/2024AIL00728139EE5599_6600
GE5243332964IL1/1/2024AIL00725281HB1437_2701

 

Thanks in advance!!

1 ACCEPTED SOLUTION
Irwan
Super User
Super User

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
)
Irwan_0-1740628008384.png

 

Hope this will help.

Thank you.

View solution in original post

6 REPLIES 6
AntrikshSharma
Super User
Super User

@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

Everything came out 0.  There are no 1's

 

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

Irwan
Super User
Super User

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
)
Irwan_0-1740628008384.png

 

Hope this will help.

Thank you.

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.  

 

Got a syntax errror

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.