topic Re: Get duplicate values in same column with iteration wise in DAX Commands and Tips

Get duplicate values in same column with iteration wise

Anonymous — Fri, 23 Sep 2022 09:31:50 GMT

Hi ,

I have a scenario where i have to find duplicated values with in a column and return 1 or 0 as a output
Excel vlookup i have - +IF(ISERROR(VLOOKUP(AI2,$AI$1:AI1,1,0))=FALSE,0,1)

the duplicate value should work as
Example - 1st row value should check duplciate with Previous value i.e..1, 0
2nd row values should check duplicate with 2,1,0
3rd row values should check duplicate with 3,2,1,0
4th row values should check duplicate with 4,3,2,1,0.........
this way the duplicate values and iteration should work

I have tried creating colum in powerbi but the output is not coming as expected. Any help on this please?

Duplicate = if(CALCULATE(count(Table [Number]), FILTER(Table,Table [Number]= EARLIER(Table [Number]))), 1, 0)

Thanks

Re: Get duplicate values in same column with iteration wise

Greg_Deckler — Fri, 23 Sep 2022 10:00:25 GMT

Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Get duplicate values in same column with iteration wise

Anonymous — Fri, 23 Sep 2022 10:19:11 GMT

Hi Greg,
Please see below the Sample output result should be 1,0
in excel by using vlookup fucntion the duplicate values are acheived by IF(ISERROR(VLOOKUP(AI2,$AI$1:AI1,1,0))=FALSE,0,1)
Now the same duplicate values need to replicate in Powerbi with calculated column

the duplication should work in such a way that

1st record in a column should check with previous records. i.e., with 0 record
2nd record should check with previous records i.e., 2 with (1 &0 records)
3rd record should check with previous records i.e., 3 with (2,1,0 records)....
Hope this is clear. Please let me know how can i achieve this in DAX

Number	Output
9900661041	1
9900661056	1
9900661056	1
9900661056	0
9900661056	0
9900661056	0

Re: Get duplicate values in same column with iteration wise

Anonymous — Fri, 23 Sep 2022 11:19:29 GMT

Hi,

Any help on this please ?

Re: Get duplicate values in same column with iteration wise

Anonymous — Fri, 23 Sep 2022 14:09:17 GMT

Hi,

Any suggestion please ?

Thanks

Re: Get duplicate values in same column with iteration wise

Anonymous — Mon, 26 Sep 2022 05:58:59 GMT

Hi Anonymous,

Any other field that can be used as an index column?
The current power bi data model table does not include row and columns index, I think you need to add an index column and use it as a condition to loop through table records.

Duplicate = VAR dCount = COUNTROWS ( FILTER ( Table, Table[Number] = EARLIER ( Table[Number] ) && Table[Index] <= EARLIER ( Table[Index] ) ) ) RETURN IF ( dCount > 1, 1, 0 )

Regards,

Xiaoxin Sheng

Re: Get duplicate values in same column with iteration wise

Anonymous — Mon, 26 Sep 2022 08:05:19 GMT

Hi.
i have tried using the formula which you shared but i am facing the error . i have removed extra brackets in return statement but still the error is coming.
Please see the screenshot attached
The source i have connected is excel and count of records is approximately 31K. Any suggestions please

Thanks

Re: Get duplicate values in same column with iteration wise

Anonymous — Mon, 26 Sep 2022 10:28:54 GMT

Hi,
Hi.
i have tried using the formula which you shared but i am facing the error . i have removed extra brackets in return statement but still the error is coming.
Please see the screenshot attached
The source i have connected is excel and count of records is approximately 31K. Any suggestions please