Forum Discussion

uif19085's avatar
uif19085
Icon for Helper III rankHelper III
3 years ago
Solved

Flag duplicates with DAX

Hello, i'm trying to find a way to flag first apprence of a duplicates across muliple columns using DAX, my table looks like this: ID           DATE             FLAG 1       01/01/2011           1 ...
  • smpa01's avatar
    3 years ago

    uif19085  can you please test out the following meaure , you probably need a rowNum/Index Column

     

     

    dup =
    VAR __val =
        CALCULATE (
            [current],
            OFFSET (
                -1,
                DISTINCT ( ALL ( 'Table' ) ),
                ORDERBY ( 'Table'[Index] ),
                KEEP,
                PARTITIONBY ( 'Table'[id] )
            )
        )
    VAR currentId =
        MAX ( 'Table'[id] )
    VAR currentIndex =
        MAX ( 'Table'[Index] )
    VAR dateCount =
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[date] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[id] = currentId
                    && 'Table'[Index] <= currentIndex
            )
        )
    VAR final =
        SWITCH (
            TRUE (),
            __val == BLANK (), 1,
            [current] - 1 = __val
                && dateCount = 1, 0,
            1
        )
    RETURN
        final
    

     

     

     

    or like this

    Measure = 
    VAR currDate =
        MAX ( 'Table'[date] )
    VAR currDate2 = currDate + 0
    VAR currentId =
        MAX ( 'Table'[id] )
    VAR currentIndex =
        MAX ( 'Table'[Index] )
    VAR minIndexbyID =
        CALCULATE (
            [currentIndex],
            INDEX (
                1,
                DISTINCT ( ALL ( 'Table' ) ),
                ORDERBY ( 'Table'[Index] ),
                KEEP,
                PARTITIONBY ( 'Table'[id] )
            )
        )
    VAR filterTbl1 =
        FILTER (
            ALL ( 'Table' ),
            'Table'[id] = currentId
                && 'Table'[Index] >= minIndexbyID
                && currentIndex >= 'Table'[Index]
        )
    VAR filterTbl2 =
        FILTER (
            ALL ( 'Table' ),
            'Table'[id] = currentId
                && 'Table'[Index] >= minIndexbyID
        )
    VAR leftTbl1 =
        SELECTCOLUMNS ( filterTbl1, "dt", 'Table'[date] + 0 )
    VAR leftTbl2 =
        SELECTCOLUMNS ( filterTbl2, "dt", 'Table'[date] + 0 )
    VAR rightTbl =
        SELECTCOLUMNS ( { currDate }, "dt", [Value] + 0 )
    VAR dates1 =
        NATURALINNERJOIN ( leftTbl1, rightTbl )
    VAR dates2 =
        NATURALINNERJOIN ( leftTbl2, rightTbl )
    VAR val1 =
        COUNTROWS ( dates1 )
    VAR val2 =
        COUNTROWS ( dates2 )
    VAR ternary =
        SWITCH (
            TRUE (),
            val1 = 1
                && val2 = 1, val1 & "-unique entry",
            val1 = 1
                && val2 <> 1, val1 & "-has duplicate entry",
            "0" & "-has duplicate entry"
        )
    RETURN
        ternary