Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Get duplicate values in same column with iteration wise

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 duplic...
DAX
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

NumberOutput
99006610411
99006610561
99006610561
99006610560
99006610560
99006610560
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi,

Any suggestion please ?

Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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