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  re...
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

     

     

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.