Find repeated values in one column with distinct values in another

[Anonymous]

This must be trivially simple but I just can't get my head around it.

How can I get a table of records that meet the following criteria:

Has repeated equal values in column A but distinct (including blank) values in column B.

Eg. if I have the following values in the source table

City	Country
Barcelona	Spain
Barcelona	France
London	UK
London	UK
Lyon	France
Madrid	Spain
Madrid
Madrid	Spain
Paris
Paris	France

The resulting table should show me which cities appear more than once and with different values in the country column, plus the count of each combination, which in this example would result in

City	Country	Count
Barcelona	Spain	1
Barcelona	France	1
Madrid	Spain	2
Madrid		1
Paris	France	1
Paris		1

London, UK would not be listed, because although there are 2 entries for it, both are in the UK (if both were blank, it would as well pass the test and not appear in the result). Lyon, France would not be listed, as it's only appearing once (no matter if country is blank or not).

Thanks in advance for any hint.

[parry2k · Accepted solution]

Add a measure as below and filter on this where value is more than 1

Distinct Count = CALCULATE(DISTINCTCOUNT( Table1[Country] ), ALLEXCEPT( Table1, Table1[City] ))

[Anonymous]

Hi @parry2k , 

I need to get a count of a specific column, however it is counting the entire row individually giving us a count of 1 for every row because of the distinct values in each row. 

How do we get it to only count only 1 isolated column while ignoring the others? 

Basically we want the debit column (Refer to the table below) to have a count of 2 for $5, and a count of 1 for the others. 

Please know that the account name and debit amount are a part of the criteria. 

In summary, we need to find accounts  that made the same amount of a transaction more than once.

Account Name	Account number	Transaction Id	Debit Amount	Country
AA	123	11	$                       5.00	usa
AA	123	12	$                       5.00	Uk
AA	123	13	$                       6.00	Uk
AB	124	14	$                       7.00	Uk
AB	124	15	$                       8.00	Uk
AB	124	16	$                       9.00	Uk
AD	126	17	$                    10.00	Uk
AE	127	18	$                       1.00	Uk

[Anonymous]

Thanks @parry2k , that's exactly what I was looking for, now I just don't understand why I couldn't figure it out myself. So simple! 

[parry2k]

solution attached