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

     

StuartSmith's avatar
StuartSmith
Power Participant
2 years ago

So I want 2 variables such as 

VAR 2ndAbsenceDate = ...

VAR 3rd AbsenceDate = ...

 

the value stored in "2ndAbsenceDate" for AbsenceID 1 would be 27/11/2023 & AbsenceID 2 would be 21/11/2023 and for "3rdAbsenceDate" for AbsenceID 1 would be 28/11/2023 & AbsenceID 2 would be 22/11/2023.

 

So simply want 2 varibles to select the 2nd and 3rd rows of each "MasterID"

  • StuartSmith's avatar
    StuartSmith
    Power Participant
    2 years ago

    Thanks for taking the time to supply a solution, but I ave used the other solution supplied by the open comtributor as I can user the "Ranking" column for additional formulas.  But I do appreciate your time and effort. ğŸ‘Š