Forum Discussion

ashuaswinireddy's avatar
ashuaswinireddy
Helper I
3 years ago
Solved

How to remove duplicate rows in calculated table based on condition

Hello All, I have below calculated table..can you please let me know how to remove duplicate ID rows in below table.  For example 123 ID is duplicated in below table and I need to remove 123 ID row...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  ashuaswinireddy ,

     

    Here are the steps you can follow：

    1. In Power query. Add Column – Index Column – From 1.

    2. Create calculated column.

    Rank =
RANKX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])),[Index],,ASC)
    Flag =
var _maxdate=MAXX(FILTER(ALL('Table'),
'Table'[ID]=EARLIER('Table'[ID])),[Date])
var _count=COUNTX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])),[ID])
return
IF(
    _count=1&&[Rank]=1,
    1,
    IF(
        _count >1&&'Table'[Date]=_maxdate,1,0)
)

    3. Create calculated table.

    Table 2 =
var _table1=
FILTER('Table',[Flag]=1)
return
SUMMARIZE(
    _table1,[ID],[link],[Date])

    4. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • mariussve1's avatar
    mariussve1
    3 years ago

    Hi again,

    Sorry for the late answer, but you can actually solve this in one DAX expression and one step:

    Calculated table =
    VAR __Table =
        SUMMARIZE (
            'Table',
            'Table'[ID],
            'Table'[Link],
            "RankColumn", ISBLANK ( 'Table'[Link] ) * 1
        )
    VAR __Rank =
        SUMMARIZE (
            __Table,
            [ID],
            [Link],
            [RankColumn],
            "Rank", RANKX (
                        FILTER(
                            __Table,
                            [ID] = EARLIER ( [ID] )
                        ),
                        [RankColumn],,ASC
                    )
        )
    VAR __Filter =
        MINX ( __Rank, [Rank] )
    VAR __Result =
        FILTER ( __Rank, [Rank] = __Filter )
    RETURN
        SUMMARIZE (
            __Result,
            [ID],
            [Link]
        )


    Br
    Marius
mariussve1's avatar
mariussve1
Solution Sage
3 years ago

Hi again,

Sorry for the late answer, but you can actually solve this in one DAX expression and one step:

Calculated table =
VAR __Table =
    SUMMARIZE (
        'Table',
        'Table'[ID],
        'Table'[Link],
        "RankColumn", ISBLANK ( 'Table'[Link] ) * 1
    )
VAR __Rank =
    SUMMARIZE (
        __Table,
        [ID],
        [Link],
        [RankColumn],
        "Rank", RANKX (
                    FILTER(
                        __Table,
                        [ID] = EARLIER ( [ID] )
                    ),
                    [RankColumn],,ASC
                )
    )
VAR __Filter =
    MINX ( __Rank, [Rank] )
VAR __Result =
    FILTER ( __Rank, [Rank] = __Filter )
RETURN
    SUMMARIZE (
        __Result,
        [ID],
        [Link]
    )


Br
Marius
Anonymous's avatar
Anonymous
Not applicable
2 years ago

I had a similar issue with duplicate Document Number - 1 with Blank Transmittal No and 1 with valid Transmittal No. I used this method to remove the blank Tr no! I like this 1 DAX query to build the desired table! Many thanks. 🙂