Forum Discussion

smerchant's avatar
smerchant
Helper I
7 years ago
Solved

Get First and Last Rows with all Columns

I have data something like this and I want get the first and last rows based on the date:

I created some measure for example like InitialScore, LastScore, FirstDate...: 

InitialScore = 
MINX(
    FILTER(
        ALL(Sheet1),
        [CatIns] = EARLIER([CatIns]) 
        && [Date] = EARLIER(Sheet1[InitialTestDate])),
        [Score])

And got results like this which is correct but..:

I have score2, 3 and more columns in the real data and I don't think should be creating measures for each column?  Is there a way to get all the rows filtered by first and last row by Category (CatIns)?

 

Thanks,

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI smerchant ,

    You can consider to do unpivot columns on your fields, then you can simply write formula to calculate first/last records based on original category and attribute group.

    Unpivot columns (Power Query)

    Regards,

    Xiaoxin Sheng

5 Replies

    • smerchant's avatar
      smerchant
      Helper I
      7 years ago

      Thanks.  I might have failed to explain.  I need to get all the columns and only first and last row by category based on the date (first and lastest)

       

      I made another attempt through an identifier column. Not sure if this is most appropriate way, although, I do get the empty row on top which I suppose I can filter out.

      Identifier = 
IF (
    MIN ( Sheet1[Date] ) = Sheet1[FirstTransactionDate],
    "First Test",
    IF (
        MAX ( Sheet1[Date] ) = Sheet1[LastTransactionDate],
        "Last Test",
        BLANK ()
    )
)

      This attempt also give the wrong sum for score columns

      • parry2k's avatar
        parry2k
        Super User
        7 years ago

        smerchant issue with your question is to get all columns in a row, it is possible to get a column, not a problem, and you have to perform the same calculation for all the columns.