cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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.

TableDetail.JPGTable with DUID on left and LastChanged on right

 

 

Thanks in advance

10 REPLIES 10
rajendran Super Contributor
Super Contributor

Re: Create new table with last complete record for each Unit

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

Re: Create new table with last complete record for each Unit

Unfortunately that function returns a table with Zero rows.

Super User
Super User

Re: Create new table with last complete record for each Unit

@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 ) )
)
Try my new Power BI game Cross the River

Re: Create new table with last complete record for each Unit

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 ?

Super User
Super User

Re: Create new table with last complete record for each Unit

@pat_energetics

 

I think You can use DISTINCT on top of it to get rid of duplicates

 

i.e.

 

Calculated Table =
DISTINCT (
    GENERATE (
        SELECTCOLUMNS ( VALUES ( 'Table1'[DUID] ), "DUID_", [DUID] ),
        CALCULATETABLE ( TOPN ( 1, 'Table1', [LastChanged], DESC ) )
    )
)
Try my new Power BI game Cross the River

Re: Create new table with last complete record for each Unit

Unfortunately it is the ties in the [Lastchanged] column that is causing the TopN to return more than 1 row for some DUID.

 

Would be useful if there was a parameter in TopN to explicitly return 1 row only - something similar to the RankX.

 

Tried wrapping the function in LastNONBlank to explicitly return only 1 row, but this doesn't appear to work, probably something to do with operation on a column argument rather than a complete table row.

 

For me, its quicker to simply export the 616 of 678 table out and remove the highlighted duplicates in excel. 

zapppsr Member
Member

Re: Create new table with last complete record for each Unit

I don't know if it is ok for you to create an auxiliary column, but if it is ok, here is my solution:

sol.png

 

Create the ISMAX column to check if it is the last record.

Then, create your virtual table with DISTINCT of only "Yes".

zapppsr Member
Member

Re: Create new table with last complete record for each Unit

 

ISMAX =
VAR DUID = Tabela1[DUID]
VAR MAXDATE =
    CALCULATE (
        MAX ( Tabela1[LASTCHANGED] );
        ALL ( Tabela1 );
        Tabela1[DUID] = DUID
    )
RETURN
    IF ( Tabela1[LASTCHANGED] = MAXDATE"Yes""No" )

 

 

UniqueDUID =
CALCULATETABLE ( DISTINCT ( Tabela1 ); Tabela1[ISMAX] = "Yes" )

 

 

Re: Create new table with last complete record for each Unit

Thanks @zapppsr, I thought your function would work.

When I filter the original table on the ISMAX column = "Yes" , it still returns 678 values (of which 616 are unique)

When I use the CALCULATETABLE with filter on the ISMAX, it still returns 678 records - with 616 unique.

 

The issue is due to the ties in the [LASTCHANGED] for some DUID, which is an issue in the source that cannot be changed.

 

In the meantime I have fixed the duplicates in excel.

 

Thanks for trying !

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 134 members 1,561 guests
Please welcome our newest community members: