Count duplicates with condition only once

[abaak]

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

[Greg_Deckler]

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

[abaak]

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]

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