cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hgalfre
Helper I
Helper I

Count duplicates depending on two columns

Hi there! 

 

I'm helping a colleague project manager and sometimes, when creating a new project, the database gives twice the same Project number to 2 different projects. 

I want him to be able to find this information quickly through the Report I'm building for him. 

 

The idea would be to create a separate page where he could see all the duplicates and then correct the database if necessary. 

 

So my idea was to either create a column in my data, that would add "1" on each row with a duplicate, or to use a measure. 

The tricky thing is that there's a status linked to this project, so I need to count a duplicate only when the project number and the status are identical (sometimes we keep the same project number for two projects on purpose, and we change the status).

 

I've managed to count duplicates for the project number, with this column formula:

Duplicata N°Projet =
IF (
COUNTROWS ( FILTER ( Projects, Projects[N° project] = EARLIER ( Projects[N° project] ) ) )
> 1,
1,
BLANK ()
)
 

So if I use it in a visual with a table, with the status and the project number, I get this:

N° projectStatusDuplicate
34350On-going2
36884On-going2
38857Won1
38857Won other project1
40433On-going2

 

As you can see, the n°34350 exists twice with the on-going status, so there's one line. However, the 38857 appears on two rows ith its two different status. If I find the right formula for the column, it shouldn't appear at all.

I want to end up with this:

 

N° projectStatusDuplicate
34350On-going2
36884On-going2
40433On-going2

 

 

I've tried this formula to upgrade the column with an additional condition on the status:

Duplicata N°Projet = IF(COUNTROWS(Projects), FILTER ( Projects, Projects[N° project] = EARLIER ( Projects[N° project] ),FILTER(Projects,Projects[Status] ) )

 

>But there are too many arguments for the filter function.

 

 

I've tried a few measures, such as: 

MesureDuplicata = COUNTROWS(
SUMMARIZE(
'Projets',
Projects[N° project],
Projects[Status]))
 
Or : 
Mesuretest = CALCULATE(
COUNTROWS(
GROUPBY(
Projects,
Projects[N° project],
Projects[Status]
)
))
 
 
 ...But I can't make them work on their own, and if I add them with the project number in a table, it counterworks; I get a list of all the project number, with a column "measure" that gives "1" to each row, except for the project numbers that are identical but with a different status (so the 38857 ends up with "2"). 
 
 
 
Has anyone has a solution?
Thank you so much!!!
1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

If you want to extend the FITLER comparison to check both the project number and the status you can use the && operator which does a logical AND.

 

eg.

 

IF(COUNTROWS( FILTER ( Projects, Projects[N° project] = EARLIER ( Projects[N° Project] ) && Projects[Status] = EARLIER(projects[Status]) ) ) > 1,1)

View solution in original post

2 REPLIES 2
hgalfre
Helper I
Helper I

Thank you! That worked fine (I had tried only with one & and without the 2nd earlier). 

d_gosbell
Super User
Super User

If you want to extend the FITLER comparison to check both the project number and the status you can use the && operator which does a logical AND.

 

eg.

 

IF(COUNTROWS( FILTER ( Projects, Projects[N° project] = EARLIER ( Projects[N° Project] ) && Projects[Status] = EARLIER(projects[Status]) ) ) > 1,1)

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.