Forum Discussion
Set a filter based on MAX
Hi Everyone,
I have tried a number of different equations and have looked through Google to no avail. I have some data I need to be able to filter based upon the maximum number in a column, however this maximum number can change.
Data exampl
The Sequence column has a number for every movement between locations. This can max at 5, 6, 10, 13 whatever, depends on the trip i.e its a changing variable each day. The Train Id and Date column will always be unique in the sense that a Train ID will only appear once in a day.
What I am trying to do is set a page filter so that only the data contained in the row with the largest sequence number will appear. I have tried using this formula:
Max sequence = CALCULATE(MAX(Table[sequence]),VALUES(Table[Train ID and Date]))
This doesnt work as each row contains a sequence number.
Hope someone can help with a formula.
Thanks,
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]))
13 Replies
I'm thinking a Measure and a Custom Column.
Measure:
Max = CALCULATE(MAX([Value]),ALL(Averages2))
Column
IsMax = IF([Value] = [Max],1,0)
Now you can set your page filter to "IsMax = 1" and you should be good to go.
Basically, the Max measure uses CALCULATE with an ALL filter to ensure that it calculates the MAX regardless of row context, etc. Then, IsMax creates a boolean value that tells you whether or not it is the MAX. To incorporate Train ID and Date, you could use an ALLEXCEPT clause in your CALCULATE filter if needed. So, think of it as all records except those filtered by this particular column kind of thing. Should get you there with a little messing around with it.
@smoupre Thanks for the response. I may of not understood your formula as I got the same result as the formula I mentioned in the intial post.
I created the max measure like this:
max = CALCULATE(MAX([Sequence]),ALL([date])) and this does indeed give you snapshot of the max sequence number, see picture below:
However when you create the custom column all values are the same "1".:
If I enter the [Sequence] data into the table and select dont summarize you can see what happens:
Each row is considered the max. This is as far as I got intially before asking for help. Its a bit of a pickle.
Thanks,
Giles
I believe the error is the ALL( Date).. In your table there is no date or ta least ( the Train ID & Date is text )..You need to add ALL( table that has the sequence ).
If is not a help then you need to give us more details..Also please add the table name is you are refering to a column so we know is a column and not a measure i.e. CALCULATE( MAX(Table1[Sequence]), ALL( 'Table2' ) )
