Forum Discussion

SrMatto's avatar
SrMatto
Icon for Helper I rankHelper I
1 year ago
Solved

Count 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))  

 

link to pbix