Filter the most recent result in a vertical table

[CEllis]

I am trying to get the most recent result based on the maximum collection point.

SourceTable

Name	Collection	Result
Name 1	1	45
Name 1	2	67
Name 2	1	54
Name 3	1	78
Name 3	2	45
Name 3	3	57
Name 4	1	78
Name 4	2	92
Name 5	1	56

I use the DAX below to create the output table but the 'Current' DAX is giving me the average of all collections not the desired output shown below.

1 = CALCULATE(AVERAGE('SourceTable'[Result]),Filter(All('SourceTable'[Collection]),'SourceTable'[Collection]=1))

2 = CALCULATE(AVERAGE('SourceTable'[Result]),Filter(All('SourceTable'[Collection]),'SourceTable'[Collection]=2))

3 = CALCULATE(AVERAGE('SourceTable'[Result]),Filter(All('SourceTable'[Collection]),'SourceTable'[Collection]=3))

MostRecent = CALCULATE(MAX('SourceTable'[Collection]))

Current = CALCULATE(AVERAGE('SourceTable'[Result]),FILTER('SourceTable','SourceTable'[Collection]=[MostRecent]))

OutPutTable

Name	1	2	3	Most Recent	Current
Name 1	45	67		2	67
Name 2	54			1	54
Name 3	78	45	57	3	57
Name 4	78	92		2	92
Name 5	56			1	56

Many thanks

Chris

[Hema_Gupta · Accepted solution]

Hi,

Hope this helps.

[CEllis]

Hi @Hema_Gupta 

Many thanks

It works with out the name in, if I add it in it trys do MAX on a text field and doesnt like it.

Current = 

Var MostRecentCollection = Max('Primary Assessment'[Collection])

RETURN

        CALCULATE(AVERAGE(Collections[Result]),
            FILTER(
                ALL(Collections),
                Collections[Collection] = MostRecentCollection
            )
        )

[juvana20]

Filter the most recent result in a vertical table by applying sorting or filtering criteria based on the timestamp or date column. This helps prioritize and display the latest data entry at the top or in a designated position within the table, enhancing readability and usability.