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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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'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