Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
3 years ago
Solved

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

Need Help
Show and Tell
  • Bifinity_75's avatar
    Bifinity_75
    3 years ago

    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

  • Bifinity_75's avatar
    Bifinity_75
    Solution Sage
    3 years ago

    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's avatar
      gauravnarchal
      Post Prodigy
      3 years ago

      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.