Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!View all the Fabric Data Days sessions on demand. View schedule
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
Solved! Go to Solution.
Hi,
Hope this helps.
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
)
)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.
Check out the November 2025 Power BI update to learn about new features.
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!