Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
asodhani
Frequent Visitor

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

1 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")

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Phil_Seamark
Employee
Employee

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")

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Hi @asodhani

 

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

 

Hi Phil,

 

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

 

Cheers

 

Abhi

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.

 

 

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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")

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark

 

Thanks for your help!

 

Its working for me now.

 

Cheers

 

Abhi

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.