How to create new column based on duplicate values

[asodhani]

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 use above in  Power Bi so wondering what,s my best option? Please advise.

Please note 1001 is repeated 2 times and I want the first one to be stated as NOT DUPLICATE and others as DUPLICATE

Contract Number	Contract Value	Duplicate Contracts
1000	100000	Not Duplicate
1001	1000000	Not Duplicate
1001	1000	Duplicate
1002	10000	Not Duplicate

[Phil_Seamark]

HI @asodhani

This calculated column should work, but it uses the [Contract Value] column to work out which is the first (non duplicate) rows.  Do you have a Date/Time column in your data that could be used instead?

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

[asodhani]

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]

Hi @asodhani

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

[asodhani]

@Phil_Seamark

Hi Phil,

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

Cheers

Abhi

[Phil_Seamark]

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]

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

[Phil_Seamark · Accepted solution]

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]

Hi @Phil_Seamark

Thanks for your help!

Its working for me now.

Cheers

Abhi