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 tab...
Need Help
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

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

    Unfortunately that function returns a table with Zero rows.

    • 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 ) )
)
      • pat_energetics's avatar
        pat_energetics
        Icon for Advocate II rankAdvocate II
        8 years ago

        Thanks Zubair_Muhammad, that seems very close.

         

        There are still some duplicate DUID values returned (616 unique of 678 rows), and we are checking the source data to see if there are stray " " or similar, or where LastUpdate is the exact same time for DUIDs. I would have expected the TOPN 1 to resolve these types of issues ?