Forum Discussion
Count duplicates with condition only once
Hi,
I want to count all numbers who are duplicated with condition Custom start once. So when 6966 has two times the condition Custom start, I want to count it as one. there is one in this list (6973) has the condition Custom start 3 times. This one I need to count as 2 times. (The first occurance of custom start is normal and is alright to happen, the second and third time this condition occurs should not happen, this is why I need to make a counter of the total errors).
When a number (12495) has condition Return and custom start I do not need to count them. Also when a number has Custom start only once I also do not need to count them (5102).
At the end it should return a 9 in a visual, can someone help me with this?
Number Condition
| 12495 | Custom Start |
| 12495 | Return |
| 2821 | Custom Start |
| 2821 | Return |
| 3729 | Custom Start |
| 6966 | Custom Start |
| 6966 | Custom Start |
| 6973 | Custom Start |
| 6973 | Custom Start |
| 6973 | Custom Start |
| 7130 | Custom Start |
| 7130 | Custom Start |
| 7444 | Custom Start |
| 7574 | Custom Start |
| 7574 | Custom Start |
| 7604 | Custom Start |
| 7604 | Custom Start |
| 7864 | Custom Start |
| 7864 | Custom Start |
| 7994 | Custom Start |
| 7994 | Return |
| 8120 | Custom Start |
| 8120 | Custom Start |
| 8137 | Custom Start |
| 8137 | Custom Start |
| 9042 | Custom Start |
| 9127 | Custom Start |
| 5102 | Custom Start |
| 2856 | Custom Start |
| 2856 | Return |
3 Replies
- Greg_Deckler
Community Champion
You 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'))
