Forum Discussion

JonKho's avatar
JonKho
Frequent Visitor
2 years ago
Solved

How to find duplicate values in one column while another column has different values?

Hi all   I need help to find the the rows in 'green' shown below. Controller_Node_Id BarCode Merged Column 10963 100 10963100 10961 100 10961100 10963 200 10963200 10963 20...
General Comment
Need Help
Show and Tell
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

Hi,

Write this calculated column formula

Column = if(CALCULATE(DISTINCTCOUNT(Data[Controller_Node_Id]),FILTER(Data,Data[BarCode]=EARLIER(Data[BarCode])))>1,"Duplicate","Unique")

Hope this helps.

 

  • JonKho's avatar
    JonKho
    Frequent Visitor
    2 years ago

    Hi Ashish_Mathur 

     

    Thank you for the reply and it is something that I want. This is what I have done

    \
    Column = if(CALCULATE(DISTINCTCOUNT(Scanned_Item_Two_Controllers[Scan_Controller_Node_Id]),FILTER(Scanned_Item_Two_Controllers,Scanned_Item_Two_Controllers[Page_Barcode]=EARLIER(Scanned_Item_Two_Controllers[Page_Barcode])))>1,"Duplicate Found!","Unique")
     

    However, I can't see the results that can be auto-refreshed. I think it is calculated coulmn allows only manual refresh only.

     

    I have create another new measure that represent what I want to do where I can detect, 'Duplicate Found!'

     

    Filter Value =
    CALCULATE(
        COUNTA('Scanned_Item_Two_Controllers'[Column]),
        'Scanned_Item_Two_Controllers'[Column] IN { "Duplicate Found!" }
    )
     
    Thank you for the help on this.

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 years ago

      You are welcome.

  • sergievs's avatar
    sergievs
    Frequent Visitor
    2 years ago