Forum Discussion
Set a filter based on MAX
GilesWalker - Try switching your ALL to an ALLEXCEPT in order to preserve the Train ID context filter:
max = CALCULATE(MAX(Train_Movements[sequence]),ALLEXCEPT(Train_Movements,[Train id]))
konstantinos Greg_Deckler Please see the screenshot of the table Train-Movements:
The sequence column changes to represent how many stops the train made along its route. The max number in the sequence could be anything (3,4,8,9,13, etc...) This max number represents the final destination.
I changed my measure to be this:
max = CALCULATE(MAX(Train_Movements[sequence]),ALL(Train_Movements))
and the calculated column is:
IsMax = IF(Train_Movements[sequence]=[max],1,0)
This was the result I got:
It seems as though the forumla is picking the highest number in the sequence column (this case was 13). If you look at the above data Train 636 had a max of 10 in its sequence, 654 had a max of 4, 677 had a max of 5.
Thanks again for your efforts with helping on this.
Giles
i'm having a very similar problem, however i'm struggling to get this to work.
is the - max = CALCULATE(MAX(Train_Movements[sequence]),ALL(Train_Movements) you mention a custom column or something else?
in my table the JAP_SEQUENCE column indicates the most recent appointment date against a specific job - JAP_JOB_REF.
I need to create a custom column like yourself which indicates a 1 against the most recent appointment (highest sequence number against the specific job) and a 0 againt old appointment, I can then filter out the 0 and have a table which only shows the most recent appointment date.