Count number of not unique row

[nooneliveforeva]

Hello all,

 For this data :

Location Date

LOCATION	DATE
LONDON	2017
PARIS	2016
LONDON	2016
L.A	2012
L.A	2005
L.A	2020
LONDON	2017

Want :

2

I want to have a total count of not unique location

Thank you very much

[fhill]

If you need the Date information, 'Duplicate' your Table in Query Editor and choose 'Group By' on the Location Column.  Use the default 'Count Rows', this gives the occurances of each Location.

On your visual (card probably) count Location and filter out 'Count'= 1.

If you need the other information, Relationship this new condensed tabel back to our original to any REALTED needs.

FOrrest

[nooneliveforeva]

Hi @fhill,

Thank you, i need this as a measure, how can i do that ?

[v-huizhn-msft]

Hi @nooneliveforeva,

I test using your formula, get exected result. Group by the table using Table3[LOCATION], then filter the summaried table where the count does not equal to 1.

Please create a measure using the formula, the "Table3" is name of sample table.

Measure = COUNTAX(FILTER(SUMMARIZE(Table3,Table3[LOCATION],"count",COUNTA(Table3[LOCATION])),[count]<>1),Table3[LOCATION])

Then create a card visual to show your result.



Best Regards,
Angelia

[nooneliveforeva · Accepted solution]

Hey @v-huizhn-msft,

Very good, thank you very much