Get duplicate values in same column with iteration wise

Question

Hi ,I have a scenario where i have to find duplicated values with in a column and return 1 or 0 as a outputExcel vlookup i have -&nbsp;+IF(ISERROR(VLOOKUP(AI2,$AI$1:AI1,1,0))=FALSE,0,1)&nbsp;the duplicate value should work as&nbsp;Example - 1st row value should check duplciate with Previous value i.e..1, 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2nd row values should check duplicate with 2,1,0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3rd row values should check duplicate with 3,2,1,0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4th row values should check duplicate with 4,3,2,1,0.........this way the duplicate values and iteration should work&nbsp;I have tried creating colum in powerbi&nbsp; but the output is not coming as expected. Any help on this please?Duplicate = if(CALCULATE(count(Table&nbsp;[Number]), FILTER(Table,Table&nbsp;[Number]= EARLIER(Table&nbsp;[Number]))), 1, 0)Thanks

greg_deckler · Answer

Anonymous&nbsp;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/1116882Also, 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/38490The most important parts are:1. Sample data as text, use the table tool in the editing bar2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.

anonymous · Answer

Hi Greg,Please see below the Sample output result should be 1,0&nbsp;in excel by using vlookup fucntion the duplicate values are acheived by IF(ISERROR(VLOOKUP(AI2,$AI$1:AI1,1,0))=FALSE,0,1)&nbsp;Now the same duplicate values&nbsp; need to replicate in Powerbi with&nbsp; calculated column&nbsp;the duplication should work in such a way that1st record in a column should check with previous records. i.e., with 0 record2nd record should check with previous records i.e., 2 with (1 &amp;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 DAXNumberOutput990066104119900661056199006610561990066105609900661056099006610560

anonymous · Answer

Hi,&nbsp;Any help on this please ?

anonymous · Answer

Hi,Any suggestion please ?Thanks

anonymous · Answer

Hi&nbsp;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] )
                &amp;&amp; Table[Index] &lt;= EARLIER ( Table[Index] )
        )
    )
RETURN
    IF ( dCount &gt; 1, 1, 0 )
Regards,
Xiaoxin Sheng

Forum Discussion

Get duplicate values in same column with iteration wise

7 Replies

Related content

remove duplicate value within a row

Replacing values with Iterations

Duplicate Value

Duplicate ranking

MAXX Returning duplicate values

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