Forum Discussion
Advocate IICreate new table with last complete record for each Unit
Kudo CollectorI don't know if it is ok for you to create an auxiliary column, but if it is ok, here is my solution:
Create the ISMAX column to check if it is the last record.
Then, create your virtual table with DISTINCT of only "Yes".
Kudo Collector
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" )
pat_energeticsThanks 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 !
- zapppsrI guess the tie is on a combination of other columns beside DUID and LASTCHANGED, because I treated the tie for those two columns. Have you considered testing creating a table with only those columns?