Countifs to identify duplicates in dataset

[adbm]

Hi,

I have the purchase register of a company and I'm trying to identify and present duplicate entries in it using power bi. In excel i would've normally used the countifs function. Eg. if my table were as below, i would add a countifs column whose formula would be COUNTIFS($C:$C,C2,$D:$D,D2). This formula would then give me a result of 2 for vendor id 600754 and 1 for the other vendor ids. the idea is to analyse all rows returning values greater than 1. Is there a way to achieve this result in PBI as well? I've gone trhough numerous posts on this topic but none of the solutions provided (consisting of using earlier / a combination of calculate, filter and countrows etc.) do not seem to work or i'm not able to customise them as per my needs. 

Thank you for your help.

A	B	C	D
Vendor ID.#	Name	Invoice No.#	Transaction Amount
600754	aaaa	123	366
989111	bbbb	234	320
242001	cccc	86030	242
600754	aaaa	123	366

[Irwan · Accepted solution]

hello @adbm 

there are couple ways to do this depend on what/how you want to show those data.

but in order to check duplicate, here are some ways. 

1. using table visual. just drop your column value and this visual will automatically show one value per same value.

2. use measure. as you can see, the duplicate value will be indicated by value over than 1.

Duplicate = 
var _Vendor = SELECTEDVALUE('Table'[Vendor ID.#])
var _Dupl = CALCULATE(COUNT('Table'[Vendor ID.#]),ALLSELECTED('Table'),'Table'[Vendor ID.#]=_Vendor)
Return
_Dupl

3. use calculated column. Similarly to measure above, but using calculated column (one with EARLIER() and another without EARLIER()).

Hope this will help.

Thank you.

[adbm]

Hi Irwan,

Thanks for that.

Solution 1 is easier but i want to be able to see the rows that are repeated so i need to see the count over 1. Solution number 2 didnt seem to work. Solution 3 with earlier worked for me. I used a calculated column as suggested by you with countrows and earlier and that seemed to work for me.

Thanks for all your help!

Best, 

Natali

[Irwan]

hello @adbm 

solution 2 need you to make a table visual since it is a measure.

but anyway, glad it works.

Thank you.