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:-

 

idStatus Priority Created LastUpdated Resolved 
12345 open high 10/03/2025 23:58 12/03/2025 23:58 12/03/2025 23:58 
12346 openhigh 10/03/2025 23:58 12/03/2025 23:58 12/03/2025 23:58 
12345 open high 08/03/2025 23:58 09/03/2025 23:58 N/I 
12346 open low 07/03/2025 21:58 08/03/2025 23:58 N/I 
23456 open low 06/03/2025 20:58 07/03/2025 23:58 N/I 
31234openmed01/03/2025 23:5805/03/2025 23:58 05/03/2025 23:58 

 

I need to remove any rows where there is a duplicate on the "id" column, keeping only the top/highest row in the table. The final table should be:-

 

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 open high 10/03/2025 23:58 12/03/2025 23:58 12/03/2025 23:58 
23456 open low 06/03/2025 20:58 07/03/2025 23:58 N/I 
31234 open med 01/03/2025 23:58 05/03/2025 23:58 

05/03/2025 23:58 

(Rows 4 & 5 have been removed as duplicates of rows 2 & 3)

 

I've tried using DISTINCT (Table and Column versions) and RANKX but can't seem to achieve the results I need.

 

Is this possible using only DAX in PowerBI? 

"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
    )

     

1 Reply

  • 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
    )