Count duplicates across distinct records

[billybazinga]

Hi community,

I've been searching the forum and the net for quite some time but am obviously being thick in how to look for an answer.

Assuming I have a table as below: I want to have a calculated column (either in DAX or M) that will count how many times the value in the ABN column exists across separate Vendor IDs. 

Vendor ID	Bank	ABN	Count of Vendor with duplicate ABN
AA	1	115566	2
AA	2	115566	2
AB	3	115500	2
AC	4	115566	2
AD	5	115500	2

In essence, the fact that Vendor ID has the same ABN across two lines is normal (and irrelevant), what I want is to flag that another Vendor ID has the same ABN. Same for vendor AB, I want to flag that vendor AD has the same ABN.

THe intent here is to provide a table with all Vendor IDs that have duplicate ABN, so I'd be ok with a new table showing this.

Thanks in advance for your help.
BBK

[v-junyant-msft]

Hi, @billybazinga ,

The following DAX might work for you:

Count of Vendor with duplicate ABN = 
CALCULATE(DISTINCTCOUNT('Table'[Vendor ID]),
ALLEXCEPT('Table','Table'[ABN])
)

The final output is shown in the following figure:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dk_dk · Accepted solution]

Hi @billybazinga ,

I have a solution with just the Power Query editor, although depending on the size of your dataset it might not be the best performance wise:

1. Duplicate your query.
2. Select the ABN and Vendor ID columns and remove duplicate rows.
3. Group by ABN and for the value use Count Rows. You should have a table that contains each ABN once and counts how many unique Vendor IDs they were listed for.
4. Merge back to the original query on the ABN column.

Alternatively you could try referencing your original query instead of duplicating. This way you wont be able to merge it back, but you could load both tables and then use LOOKUPVALUES in a calculated column to get the count into your original table. I am honestly not sure which method would be better for performance.

I hope this helps, let me know if you have any questions.

[billybazinga]

Thanks very much @dk_dk , it worked a treat 🙂