Forum Discussion
Filtering table by most recent data (with multiple different last entry dates)
I'd solve it by create a True/False DAX column like this:
displayInReport = VAR rowDate = [Project Update] VAR projectName = [Project name] RETURN CALCULATE( MAX('YourTable'[Project Updates]) = rowDate, ALL('YourTable'), 'YourTable'[Project name] = projectName )Then filtering on this column for TRUE.
Hi AaronM
As tested, Ross73312's method is helpful, you need to create a caluclated column with his formula,
then add this column in a slicer, add other columns in a Table visual,
finally when you select "true" from that slicer, the table would show the lastest data for each project.
Or you could create a measure, then add this measure in the Visual Level filter, select "show items when value is 1".
Measure =
IF (
MAX ( [Project Update] )
= CALCULATE (
MAX ( [Project Update] ),
ALLEXCEPT ( 'DATA SET EXAMPLE', 'DATA SET EXAMPLE'[Project name ] )
),
1,
0
)Best Regards
Maggie
Hi AaronM
As tested, Ross73312's method is helpful, you need to create a caluclated column with his formula,
then add this column in a slicer, add other columns in a Table visual,
finally when you select "true" from that slicer, the table would show the lastest data for each project.
Or you could create a measure, then add this measure in the Visual Level filter, select "show items when value is 1".
Measure =
IF (
MAX ( [Project Update] )
= CALCULATE (
MAX ( [Project Update] ),
ALLEXCEPT ( 'DATA SET EXAMPLE', 'DATA SET EXAMPLE'[Project name ] )
),
1,
0
)
Best Regards
Maggie
Hi Maggie,
I'm interested in your response here ... and think the Measure is a smarter way to go, but struggling to get the same result.
I have 2 tables ... normalised. For ease of management, I'll write them simplistically below:-
Project Table
=========
ID
Project Name
Project-Status Table
=============
Week Ending
Status
ID-Project
The output table visual would have the fields
Week Ending | Project Name | Status .... plus other fields
The relationships are managed in the dataset model.
How do I amend the measure shown above to get the same result and only show the latest status for each project in the list (where week endings might all be different periods for different projects). I've tried several versions of the Measure and can't seem to get it to work - I get the 0 but not the 1.
Many thanks
Andre