please help me find Duplicates

[sharonmathew]

http://powerpivotforum.com.au/viewtopic.php?f=6&t=1055

Kindly hlp me identify the duplicates.

[Zubair_Muhammad]

Hi @sharonmathew

You can add this calculated column to your Table

=
IF ( CALCULATE ( COUNTROWS ( TableName ) ) > 1, "Duplicates" )

[Zubair_Muhammad]

@sharonmathew

[sharonmathew]

Hi thanks,

but can we get a measure:=,

it is attractive than cc.. i love measures.

[sharonmathew]

the results can be like this.

Airline	Date	ICAO Flight No	From	To	Aircraft Type	Count of Date
Air Canada	18-02-2018	ACA17	Toronto Pearson	Tokyo Narita	Boeing 787-9	3
Air Canada	18-02-2018	ACA17	Toronto Pearson	Tokyo Narita	Boeing 787-9	3
British Airways	20-02-2018	BAW19	London City	New York JFK	Airbus A318	2
British Airways	20-02-2018	BAW19	London City	New York JFK	Airbus A318	2
Emirates	25-02-2018	UAE114	Dubai	London Heathrow	Airbus A380-800	2
Emirates	25-02-2018	UAE114	Dubai	London Heathrow	Airbus A380-800	2

[sharonmathew]

but there is one more,

the aircraft type is not necessarily be duplicated to be duplicate. so we may have many other fields.

when i prepared the data, i selected the fields of interest. so it would be desirable if we have the fields inside the measure under a filter i believe

[Zubair_Muhammad]

@sharonmathew

As a MEASURE, try this

=
IF ( COUNTROWS ( TableName ) > 1, COUNTROWS ( TableName ) )

[Zubair_Muhammad]

@sharonmathew

MEASURE are dynamic.... SO whatever combination of fields you use in a PIVOT TABLE..... This MEASURE would give you the count of duplciates for that

[sharonmathew]

Ok, thanks, but perhaps, my query, needs a bit rephrasing, as i might not have been explained that part.

when i pull one field, all the fields to be applied in the measure without all the fields being pulled.

the measure you have given me does work like charm,  i do not have any disagreement. 

but please give me a measure which would give me the even when we put one of the fields in the row label.

with all the fields being considered.

so if we place Airline field. then it calculates the duplicates considering all the fields in the data table. [but there may bemore fields which we need not consider for this calculation] it gives me the all the entries of duplicates. just as a data table sorted with duplicate entries.

[Zubair_Muhammad]

HI @sharonmathew

Please could you give me an example of OUTPUT desired.

I will try to help.

[sharonmathew]

ok, if we place Airline [ or any of the fields which is shown on the data table]

we get a table with measure tells us each row by row which is 1st entry and 2nd entry and 3rd entry.

so here i dont necessarily have to pull all the fields [

Airline

ICAO Flight No

From

To

Aircraft Type

Date

result looks like this.

Airline	Date	Measure
Air Canada	43149	1
Air Canada	43149	2
Air Canada	43149	3
British Airways	43151	1
British Airways	43151	2
Emirates	43156	1
Emirates	43156	2

or

Airline	ICAO Flight No	Date	Measure
Air Canada	ACA17	18-Feb-18	1
Air Canada	ACA17	18-Feb-18	2
Air Canada	ACA17	18-Feb-18	3
British Airways	BAW19	20-Feb-18	1
British Airways	BAW19	20-Feb-18	2
Emirates	UAE114	25-Feb-18	1
Emirates	UAE114	25-Feb-18	2