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'))
