Forum Discussion
SrMatto
Helper I
1 year agoCount most recent value with a rule
Hi,
I'm trying to achieve the following table representation:
For each Activity, count the number of IDs that are doing it in their most recent DateID.
Important: there are duplicates, so keep only the alphabetical MAX.
Expected:
FactActivity table:
My data model is:
My current calculation is wrong because it is affected by the row context. (the total count should be 4)
Count Most Recent Activity =
VAR LatestDate= CALCULATE( MAX(FactActivity[DateID]), ALLEXCEPT(FactActivity, FactActivity[ID]) )
RETURN
COUNTX(FactActivity, CALCULATE(MAX(FactActivity[Activity]), FactActivity[DateID]=LatestDate))