Removing row data from a calculation

[Tyron]

I am trying to remove rows of data from an imported excel file to find the proper count of an enquiry type.

I have three columns of Date, ReferenceNo and Enquiry Type. 

When an Assigned enquiry type is created, automatically a new row of data is created with an Edit enquiry type. So you get two rows of data for one action.

I would like to count only the enquiry type Edit but not the Edit that is associated with the enquiry type Assigned which are related by the duplicated ReferenceNo.

Here is a sample table noting the duplicate Reference number. The correct count of Edit should be 6 and not 10.

Date	ReferenceNo	Enquiry Type
10/05/2019	000-012	Assigned
10/05/2019	000-012	Edit
10/05/2019	000-123	Assigned
10/05/2019	000-123	Edit
10/05/2019	000-456	Edit
10/05/2019	000-789	Edit
10/05/2019	000-963	Edit
10/05/2019	000-852	Edit
10/05/2019	000-741	Edit
10/05/2019	000-753	Assigned
10/05/2019	000-753	Edit
10/05/2019	000-951	Assigned
10/05/2019	000-951	Edit
10/05/2019	000-486	Edit

Thanks in advance for any assistance given.

[Ashish_Mathur]

Hi,

This measure works

=COUNTROWS(FILTER(SUMMARIZE(Data,Data[ReferenceNo],Data[Enquiry Type],"ABCD",CALCULATE(DISTINCTCOUNT(Data[Enquiry Type]),ALL(Data[Enquiry Type]))),Data[Enquiry Type]="Edit"&&[ABCD]=1))

Hope this helps.

[Tyron]

Thanks @Ashish_Mathur 

Unfortunately I can get that unique number already but don't think I explained myself well enough.

Each reference number is worked on by a person. It creates two entries automatically when the person assigns the reference number to another person. So we have a fourth column with the person's name which is repeated based on what reference number they work on. Usually one person assigns reference numbers to other people and that makes their unique edit count high. I am essentially wanting to count reference numbers where the person only edits and not assigned and therefore edited. So counting unique reference numbers but not when they are repeated with Enquiry Type = Assigned.

I'd prefer not to go through the Excel file to manually delete the two lines for Assigned and Edit.

Hope that makes sense and appreciate any assistance given.

[Anonymous]

Why not just remove the duplicates of ReferenceNo?
it will keep the ones with Type Assignes since they are mentioned before the Edit ones. 

The edits will then only be true edits.

[Tyron]

The issue I am finding is that with more rows of data I get a number returned but that number is different to manually removing the Edit and Assigned Enquiry Type that share the same Reference No.

Thanks for the input so far, appreciated.

[Ashish_Mathur]

Hi,

Unfortunately, i do not think i understand your requirement.  I got the exact result you wanted (as mentioned in your first post).