Match values in different columns

[gauravnarchal]

Hello

I need your help to match the data from my table as

• Type 1 | Order No - Order Date - Delivery date

         Equals

• Type 2 | Order No - Order Date - Delivery date

• If-Match  return “Matched”
• If unmatch  return “Unmatch”
• If Type 2 information is missing then return “Missing”

Is it possible to create a measure to get the above results?

Result

Type	Order No	Order Date	Delivery Date	Result
1	8767879	10-Jan-22	22-Jan-22	Matched
2	8767879	10-Jan-22	22-Jan-22	Matched
1	987190	12-Jan-22	24-Jan-22	Matched
2	987190	12-Jan-22	24-Jan-22	Matched
1	187979	12-Jan-22	24-Jan-22	Missing
1	12657	20-Feb-22	04-Mar-22	Unmatched
2	12657	20-Feb-23	04-Mar-23	Unmatched
1	3121	19-Jun-22	29-Jun-22	Unmatched
2	3121	19-Jun-22	30-Jun-22	Unmatched

Thanks

Click here for PBIX data

[Bifinity_75 · Accepted solution]

Hi @gauravnarchal , try this calculate column:

Result = var order_p=CALCULATE(count(Table1[Order No]),ALLEXCEPT(Table1,Table1[Order No]))
var order_date_p=CALCULATE(DISTINCTCOUNT(Table1[Order Date]),ALLEXCEPT(Table1,Table1[Order No]))
var delivery_date_p=CALCULATE(DISTINCTCOUNT(Table1[Delivery Date]),ALLEXCEPT(Table1,Table1[Order No]))
return
SWITCH(
        TRUE(),
        order_p=1, "Missing",
        order_p=2 && order_date_p=1 && delivery_date_p=1, "Matched",
        "Unmatched"
)

The result:

Best regards

[gauravnarchal]

Hi @Bifinity_75  - The solution provided by you did work. There is a small change that is needed to the DAX.

I want to count the order no of each order serial, so have to change the below part

var order_p=CALCULATE(count(Table1[Order No]),ALLEXCEPT(Table1,Table1[Order No]))

The reason I would need to count the order no of each order serial is that the order numbers are getting duplicated in my data so have to narrow down the count based on the order serial.

I have attached the updated PBIX which is having the order serial. Click Here

Thank you for your help.