Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Find repeated values in one column with distinct values in another

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

 

CityCountry
BarcelonaSpain
BarcelonaFrance
LondonUK
LondonUK
LyonFrance
MadridSpain
Madrid 
MadridSpain
Paris 
ParisFrance

 

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

 

CityCountryCount
BarcelonaSpain1
BarcelonaFrance1
MadridSpain2
Madrid 1
ParisFrance1
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.

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

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] ))


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

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] ))


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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 NameAccount numberTransaction Id Debit Amount Country
AA12311 $                       5.00usa
AA12312 $                       5.00Uk
AA12313 $                       6.00Uk
AB12414 $                       7.00Uk
AB12415 $                       8.00Uk
AB12416 $                       9.00Uk
AD12617 $                    10.00Uk
AE12718 $                       1.00Uk
Anonymous
Not applicable

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! 

solution attached



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.