cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## Match values in different columns

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

1 ACCEPTED SOLUTION
Solution Sage

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

2 REPLIES 2
Solution Sage

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

Post Prodigy

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.