Filter on a measure column in table

[Nellie115]

Hello, 

I'm new again and I'm trying to isolate duplicate tracking numbers due to a system problem. Our delivery number field is what I wanted to count distinct, so if is 1 tracking number and 2 deliveries those are the duplicates. 

How can I isolate just the column with the result of 2 from count(distinct[delivery number]). I tried count(distinct[delivery number]='2'), but that only shows with 1.

Any help would be appreciated 

[bharathadde]

Try this one

if you want count of dulicates =2

=Count (If(Aggr(Count([delivery number]),[delivery number])=2,[delivery number]) )

or  if you want count any duplicates ( like 2 or 3 or 4 .....)

=Count (If(Aggr(Count([delivery number]),[delivery number])>1,[delivery number]) )

[Nellie115]

Hi Bharathadde, 

this does not work, only shows zero '0' as a result.

[bharathadde]

can you share sample data?

Below is the data I used for calculation

Load * inline [
Deliverynumber
1
1
2
2
3
4
5
5
6
6
7
];

[Nellie115]

columns are as follows. The count of 2 are duplicate tracking numbers for different delivery numbers. 

Package ID               Warehouse                     Count(distinct[delivery number])

1234                                    1                                                              1

2345                                     2                                                              2

5649                                    3                                                              2

2315                                     4                                                              2