Forum Discussion
Count duplicates with condition only once
Community ChampionYou should be able to do something like:
Measure =
VAR __table = SUMMARIZE('Table','Table'[Number],"__custom",COUNTROWS(FILTER('Table',[Condition]="Custom Start")),"__return",COUNTROWS(FILTER('Table',[Condition]="Return")))
VAR __table1 = FILTER(__table,[__return]<1)
VAR __table2 = ADDCOLUMNS(__table1,"__final",1)
RETURN
SUMX(__table2,[__final])
Sorry for late response, went on holliday!
I thinkt something went wrong with explaining.
The only thing it has to count when there is a duplicate of the Number. After that when there is a duplicate in the condition as Custom start. It has to count this as 1. When a Number apears 3 times and the custom start is there 3 times aswell it has to be a 2. For four times it has to be a 3. (see duplicates row in table below, I do not have this row in the table itself)
| Number | Condition | Duplicates |
| 12495 | Custom Start | 0 |
| 12495 | Return | 0 |
| 2821 | Custom Start | 0 |
| 2821 | Return | 0 |
| 3729 | Custom Start | 0 |
| 6966 | Custom Start | 1 |
| 6966 | Custom Start | 1 |
| 6973 | Custom Start | 2 |
| 6973 | Custom Start | 2 |
| 6973 | Custom Start | 2 |
| 7130 | Custom Start | 1 |
| 7130 | Custom Start | 1 |
| 7444 | Custom Start | 0 |
| 7574 | Custom Start | 1 |
| 7574 | Custom Start | 1 |
| 7604 | Custom Start | 1 |
| 7604 | Custom Start | 1 |
| 7864 | Custom Start | 1 |
| 7864 | Custom Start | 1 |
| 7994 | Custom Start | 0 |
| 7994 | Return | 0 |
| 8120 | Custom Start | 1 |
| 8120 | Custom Start | 1 |
| 8137 | Custom Start | 1 |
| 8137 | Custom Start | 1 |
| 9042 | Custom Start | 0 |
| 9127 | Custom Start | 0 |
| 5102 | Custom Start | 0 |
| 2856 | Custom Start | 0 |
| 2856 | Return | 0 |
I don't know if it will go like this with the duplicate row. now it only has to count the duplicate one time per number. So then you will get a total of 9. (7 numbers are duplicated once and 1 number is duplicated twice. This makes 9).
My data table is way bigger but what I get with your measure is way to high then I should. Can you help me any further?
- Cmcmahan
Resident Rockstar
You should be able to make a minor adjustment to Greg's query then. Notice the section in blue, which makes the measure ignore the first instance of a number/condition pair.
CountDupes = VAR __table = SUMMARIZE('Table', 'Table'[Number], 'Table2'[Condition], "Dupes", COUNTROWS('Table')-1) RETURN SUMX(__table, [Dupes])You can also go about it this way, which has better performance, as long as the only columns in your input table are Number and Condition:
CountDupesAlt = COUNTROWS('Table') - COUNTROWS(DISTINCT('Table'))
