Forum Discussion

famousmouse's avatar
famousmouse
Regular Visitor
1 year ago
Solved

Using only DAX (not powerquery) drop duplicates based on a single column

I have a table similar to the following:-   id Status  Priority  Created  LastUpdated  Resolved  12345  open  high  10/03/2025 23:58  12/03/2025 23:58  12/03/2025 23:58  12346  ...
"DAX"
"Power BI"
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    famousmouse First, create a calculated column to rank the rows based on the "id" column and any other criteria you want to use to determine the "top/highest" row. For example, you can use the "Created" date to determine the most recent entry.

     

    RankColumn =
    RANKX(
    FILTER(
    'YourTable',
    'YourTable'[id] = EARLIER('YourTable'[id])
    ),
    'YourTable'[Created],
    ,
    DESC,
    DENSE
    )

     

    Create a calculated table to remove duplicates

    FilteredTable =
    FILTER(
    'YourTable',
    'YourTable'[RankColumn] = 1
    )

     

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

famousmouse First, create a calculated column to rank the rows based on the "id" column and any other criteria you want to use to determine the "top/highest" row. For example, you can use the "Created" date to determine the most recent entry.

 

RankColumn =
RANKX(
FILTER(
'YourTable',
'YourTable'[id] = EARLIER('YourTable'[id])
),
'YourTable'[Created],
,
DESC,
DENSE
)

 

Create a calculated table to remove duplicates

FilteredTable =
FILTER(
'YourTable',
'YourTable'[RankColumn] = 1
)