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° project | Status | Duplicate |
| 34350 | On-going | 2 |
| 36884 | On-going | 2 |
| 38857 | Won | 1 |
| 38857 | Won other project | 1 |
| 40433 | On-going | 2 |
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° project | Status | Duplicate |
| 34350 | On-going | 2 |
| 36884 | On-going | 2 |
| 40433 | On-going | 2 |
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!!!
