Skip to main content
cancel
Showing results for 
Search instead 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

Reply
gauravnarchal
Post Prodigy
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

 

TypeOrder NoOrder DateDelivery DateResult
1876787910-Jan-2222-Jan-22Matched
2876787910-Jan-2222-Jan-22Matched
198719012-Jan-2224-Jan-22Matched
298719012-Jan-2224-Jan-22Matched
118797912-Jan-2224-Jan-22Missing
11265720-Feb-2204-Mar-22Unmatched
21265720-Feb-2304-Mar-23Unmatched
1312119-Jun-2229-Jun-22Unmatched
2312119-Jun-2230-Jun-22Unmatched

 

Thanks

 

Click here for PBIX data

1 ACCEPTED SOLUTION
Bifinity_75
Solution Sage
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:

Bifinity_75_0-1669884046984.png

 

Best regards

 

 

View solution in original post

2 REPLIES 2
Bifinity_75
Solution Sage
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:

Bifinity_75_0-1669884046984.png

 

Best regards

 

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors