Forum Discussion

pat_energetics's avatar
pat_energetics
Icon for Advocate II rankAdvocate II
8 years ago

Create new table with last complete record for each Unit

Hi,

 

I'm trying to create a new table containing only the latest record for each unique "DUID" in the column DUID of the table shown using the maximum value from the "LastChanged" column.

The table shown is related * -> 1 to a table of unique DUID's, but no other relationships to date (yet). I have tried using Calculatetable ,  Allexcept (Table,Table(DUID)) , FILTER(Table,max(lastchanged) without luck. Summarize seems another approach used in other posts, but thought there has to be an easier way to produce it than recreating all the columns again.

Other posts using lastdate also appear to have had issues when the field is datetime.

Table with DUID on left and LastChanged on right

 

 

Thanks in advance

Need Help

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi

     

    Try this one and let me know if you see any issues.

     

    New Table = FILTER( Table1, Table1[DUID]= MAX(Table1[DUID]) && Table1[lastChanged]=MAX(Table1[lastChanged]))

     

    Thanks
    Raj

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        pat_energetics

         

        Try this calculated table

        from Modelling Tab>>New Table

         

        Calculated Table =
GENERATE (
    SELECTCOLUMNS ( VALUES ( 'Table1'[DUID] ), "DUID_", [DUID] ),
    CALCULATETABLE ( TOPN ( 1, 'Table1', [LastChanged], DESC ) )
)