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
AllisonKennedy's avatar
AllisonKennedy
Community Champion
3 years ago

ashuaswinireddy 

 

If you want to remove the rows completely from the report, it's more efficient to do this in Power Query than DAX.

 

If you just want to keep the non blank links, then click 'Transform Data' to open power query. Filter the Link column to exclude null and blank values, then Close and Apply changes. 

 

If you want to remove duplicates and keep only the latest date, then Sort by Date in Power Query first. You'll need to add Table.Buffer to ensure the sort stays for the next step as per this answer: Solved: How do I buffer my table in Power Query? - Microsoft Power BI Community

Then right click on the ID column and remove duplicates. Finally, close and Apply.