Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!!
Solved! Go to 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.
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
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.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |