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
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]))
GilesWalker - Cool, glad we could arrive at a solution.
The ALLEXCEPT formula removes all context except for filters applied to the specified column(s) as explained here:
https://msdn.microsoft.com/en-us/library/ee634795.aspx
So, basically the ALL filter was removing all context from the CALCULATION, meaning that it returned the highst (MAX) value from all of the rows.
The ALLEXCEPT preserves the "Train Id" context but removes all other context like date, etc, meaning that the use of ALLEXCEPT returns the highest (MAX) value from all of the rows filtered by the "Train Id" context of the row of the visualization, and ONLY the Train Id context of the visualization row.
If you are truly thinking DAX, you are thinking in terms of the "context" in which the DAX formula is being executed. Weird, because most other languages do not so heavily feature context as a thing, but DAX does. In my opinion, this is one of the most powerful things about DAX is that you can essentially create dynamic "views" of the same data, the views basically translating to context. Generally in most other languages, there is a single context by which you process information. The flexibility of dynamically creating context makes DAX super cool and powerful, but it is a learning curve at the same time.
Greg_Deckler - I found this page which described it well:
https://msdn.microsoft.com/en-us/library/ee634795.aspx
Thansk again.
Giles
Greg_Deckler You are a legend. Thank you for sorting this out for me. Are you able to explain how the formula works with the ALLEXCEPT?
Thanks,
Giles