Forum Discussion

StuartSmith's avatar
StuartSmith
Power Participant
2 years ago
Solved

Get 2nd & 3rd Rows in a column.

I have a table similar to the one below... MasterID   AbsenceDate   Name   AbsenceID   1   24/11/2023   John Doe   511   1   27/11/2023   John Doe   512   1   28/1...
Need Help
  • danextian's avatar
    danextian
    2 years ago

    hI StuartSmith ,

     

    First create a rank by masterid calculated column:

    Absence Date Rank by MasterID = 
IF (
    WEEKDAY ( 'Table'[AbsenceDate], 2 ) <= 5,
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            WEEKDAY ( 'Table'[AbsenceDate], 2 ) <= 5
                && 'Table'[MasterID] = EARLIER ( 'Table'[MasterID] )
        ),
        'Table'[AbsenceDate],
        ,
        asc,
        DENSE
    )
)

    and then to get the 2nd and 3rd rows

     

danextian's avatar
danextian
Super User
2 years ago

hI StuartSmith ,

 

First create a rank by masterid calculated column:

Absence Date Rank by MasterID = 
IF (
    WEEKDAY ( 'Table'[AbsenceDate], 2 ) <= 5,
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            WEEKDAY ( 'Table'[AbsenceDate], 2 ) <= 5
                && 'Table'[MasterID] = EARLIER ( 'Table'[MasterID] )
        ),
        'Table'[AbsenceDate],
        ,
        asc,
        DENSE
    )
)

and then to get the 2nd and 3rd rows

 

  • StuartSmith's avatar
    StuartSmith
    Power Participant
    2 years ago

    Let me test and get back to you. Thanks 

  • StuartSmith's avatar
    StuartSmith
    Power Participant
    2 years ago

    Thanks, that worked.