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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.