Pick Top Row in a List Not Working

[sandy_liew]

Hi, I have the following table and would like to only show 1 row by taking the top row based on the earliest output by spec move-in date time so that I can remove duplicates in the lot ID as there will be multiple transactions resulting in multiple specs to be recorded and hence duplicated lot IDs, my plan is to use the MIN dax function to obtain the earliest move in date time and then flag the top row as '1' while the rest as '0', I started the following DAX and is now facing issues on how to continue it , I wanted to try exploring via topn/rank/hasonevalue function.

In this case for lot ID KLPE13CYF000, based on output by spec move-in date time, the top row will be the one shown in red box (4/30/2025 15:50:15), hence it should be flagged as '1' while the subsequent rows will be as '0' and the same concept will be applied for other lot IDs:

Current DAX:

EarliestMoveInDateTime=Calculate(Min('F-Spec Output 2'[Output by Spec Move-In Date Time])

Your help is much appreciated.

Regards,
Sandy

[DataNinja777 · Accepted solution]

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,

[v-kpoloju-msft · Accepted solution]

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.

[v-kpoloju-msft]

Hi @sandy_liew,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @sandy_liew,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @sandy_liew,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[MarkLaf]

It looks like you want a measure so you can have 1/0 in a table visual? Something like this would work, I think.

IsTopRow = 
VAR _curDt = 
    MIN( 'F-Spec Output 2'[Output by Spec Move-In Date Time] )
VAR _minLotDt = 
    CALCULATE( 
        MIN( 'F-Spec Output 2'[Output by Spec Move-In Date Time] ), 
        REMOVEFILTERS( 'F-Spec Output 2' ), 
        VALUES( 'F-Spec Output 2'[Lot ID] ) 
    )
RETURN
    CONVERT( _curDt = _minLotDt, INTEGER )

[DataNinja777 · Accepted solution]

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,

[sandy_liew]

I tried adding your DAX but it returns me the following result will all rows 0: