Show all columns for the record with max date in a table visual

[AsmJuv2004]

Hi Team,

I have data in the following format.

Col1	Date	Col4	Col5
A	10/11/2024	Sp1	Ap1
A	10/12/2024	Sp2	Ap2
B	10/11/2024	Sp2	Ap3
B	10/9/2024	Sp1	Ap2

I need to show all the columns in a table visual. I need to show 1 record per col1 with max date and corresponding values of col4 and col5. Max date need to be calculated only based on col1.

The result I'm looking for is this.

Col1	Date	Col4	Col5
A	10/12/2024	Sp2	Ap2
B	10/11/2024	Sp2	Ap3

Any help would be appreciated. Please note I need to do this in the frontend itself. 

[MatheusC · Accepted solution]

There are better approaches, as mentioned in the graph, I thought of something like the dimension function:

Aggr(Date( Max(Date),'MM/DD/YYYY'), Col1)

and for another column, you can work with the conditional and remove the null value in the dimension option.

=If(Aggr(NODISTINCT Max(Date),Col1) = Date, Col4)


- Regards, Matheus

[Kushal_Chawda · Accepted solution]

@AsmJuv2004  With Col1,Col4 and Col5 in the dimension, try  below calculated dimension for Date

aggr(if( Date =date(max(total <Col1>Date)),Date),Col1,Date)

[AsmJuv2004]

Both solutions provided works for me. Thanks so much for the help!!!