Forum Discussion

hgalfre's avatar
hgalfre
Icon for Helper I rankHelper I
5 years ago
Solved

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!!!
  • 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)

2 Replies

  • 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)
  • Thank you! That worked fine (I had tried only with one & and without the 2nd earlier).