Forum Discussion

lscoffield's avatar
lscoffield
Frequent Visitor
7 years ago

Create a new table with latest row

 

I have a table that records activities session_id and a timestamp. (see diagram) I would like to create a table with only the latest entry for each session_id. The rows marked in yellow. Can someone please help.

 

Need Help

3 Replies

  • davehus's avatar
    davehus
    Icon for Memorable Member rankMemorable Member
    7 years ago

    Hi, 

     

    In Powerbi Select Modelling > New Table and try the code below.

     

    Last Date = SUMMARIZE('Your Table', 'Your Table'[Session ID], "Last Date", MAX('Your Table'[TimeStamp]))

    HTH

     

    D

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi lscoffield,

     

    You also can add create a measure and add it in the Visual Level filter and filter out the blanks.

    Measure =
VAR maxTimeStamp =
    CALCULATE (
        MAX ( 'table'[timestamp] ),
        ALLEXCEPT ( 'table', 'table'[session_id] )
    )
RETURN
    IF ( MIN ( 'table'[timestamp] ) = maxTimeStamp, 1, BLANK () )

    Best Regards,

    Dale