cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nooneliveforeva
Frequent Visitor

Count number of not unique row

Hello all,

 For this data :

 

Location Date

LOCATIONDATE
LONDON2017
PARIS2016
LONDON2016
L.A2012
L.A2005
L.A2020
LONDON2017

 

Want :

2

I want to have a total count of not unique location

Thank you very much

1 ACCEPTED SOLUTION

Hey @v-huizhn-msft,

Very good, thank you very much Smiley Very Happy

View solution in original post

4 REPLIES 4
fhill
Super User
Super User

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to be a Super User helping give back to the community!
Thank You!




Hi @fhill,

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

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.

2.PNG

Best Regards,
Angelia

Hey @v-huizhn-msft,

Very good, thank you very much Smiley Very Happy

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors