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,

 

If you want to keep certain rows duplicated on the ID you need to use rank function:

https://dax.guide/rankx/

If you need more help, Please let me know How to rank (wich rules do you want to use to keep the correct row)

 

Br

Marius

  • ashuaswinireddy's avatar
    ashuaswinireddy
    Helper I
    3 years ago
    Thank you for your response!
     
    I used below formula to create above mentioned table. can you please let me knopw how to use Rankx in this formula to filter out duplicate values for each id
     
    table = SUMMARIZE('Links table',' Links table'[id],'Links table'[link],"rtc link date",MAX(' Links table'[date])
     
    thank you!