Forum Discussion

asodhani's avatar
asodhani
Frequent Visitor
8 years ago
Solved

How to create new column based on duplicate values

I want to create a column Duplicate Contracts . I use to do this in excel by   B3 and B2 are row numbers for Contract Number for column.   =IF((B3-B2)=0,"Duplicate","Not Duplicate")   I cannot ...
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Then just change the code from the original calculation I sent to use the name of the index column you just added where I have highlighted below.

     

     

    Column = 
VAR CountOfRows = 
    CALCULATE(
            COUNTROWS('Table1'), 
            FILTER( 'Table1' , 
            'Table1'[Contract Number] = EARLIER('Table1'[Contract Number]) && 
            'Table1'[Index Column] > EARLIER('Table1'[Index Column])
            )
            )+0
RETURN IF(CountOfRows=0,"Not Duplicate","Duplicate")

     

asodhani's avatar
asodhani
Frequent Visitor
8 years ago

Hi Phil,

Thanks for the feedback. The contract value, date and time of creation are all exactly the same.

 

The table I presented should have looked like below. My mistake I added contract value incorrectly.

 

Contract Number

Contract Value

Duplicate Contracts

1000

100000

Not Duplicate

1001

1000000

Not Duplicate

1001

1000000

Duplicate

1002

10000

Not Duplicate

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi asodhani

 

Do you have any other columns that can be used to split the tie for the 1001 record?  

 

  • asodhani's avatar
    asodhani
    Frequent Visitor
    8 years ago

    Phil_Seamark

     

    Hi Phil,

     

    There are no other columns which have different value. Its a exact replica.

     

    Cheers

     

    Abhi

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      This will be an issue for DAX.  If the data in all columns is identical for multiple rows, the calculation will return the same.

       

      Can you add an Index column to the data in the query editor?  This will at least provide something for DAX to work with.

       

       

       

      • asodhani's avatar
        asodhani
        Frequent Visitor
        8 years ago

        Hi Phil_Seamark

         

        I added a Index Column now. Please let me know if you have any further thoughts.

         

        Thank you for your help to date.

         

        Cheers