Pick Top Row in a List Not Working

Hi sandy_liew ,

To solve the issue of duplicate Lot IDs caused by multiple spec transactions, and to retain only the row with the earliest "Output by Spec Move-In Date Time" for each Lot ID, you can use a calculated column with DAX that ranks the rows within each Lot ID group. This allows you to flag only the top-ranked row — the one with the earliest timestamp — and ignore the rest. Here's how you can do it using RANKX:

IsTopRow = 
VAR CurrentLotID = 'F-Spec Output 2'[Lot ID]
VAR RankByDate = 
    RANKX(
        FILTER(
            'F-Spec Output 2',
            'F-Spec Output 2'[Lot ID] = CurrentLotID
        ),
        'F-Spec Output 2'[Output by Spec Move-In Date Time],
        ,
        ASC,
        Dense
    )
RETURN IF(RankByDate = 1, 1, 0)

This formula filters the table down to only rows with the same Lot ID, ranks them by their move-in timestamp in ascending order, and returns 1 for the earliest row (i.e., rank 1) and 0 otherwise. You can then use this column in your visual to filter out the duplicate rows by keeping only the rows where IsTopRow equals 1. This approach keeps your dataset clean while ensuring you don't lose important transactional information tied to the earliest spec activity per lot.

Best regards,

Hi sandy_liew,
Thank you for reaching out to the Microsoft fabric community forum. Thank you MarkLaf, DataNinja777, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Create column in F-Spec Output 2 table:

IsEarliestMoveIn =

IF([RankByMoveInDate] = 1, 1, 0)

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.