Get duplicate values in same column with iteration wise

[Anonymous]

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

[Anonymous]

Hi, 

Any help on this please ?

[Greg_Deckler]

@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.

[Anonymous]

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

[Anonymous]

Hi,

Any suggestion please ?

Thanks

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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