Forum Discussion

GilesWalker's avatar
GilesWalker
Skilled Sharer
10 years ago
Solved

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, howev...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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's avatar
konstantinos
Memorable Member
10 years ago

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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

Thanks for clarifying the formula konstantinos. I shouldn't have left it like I did with my testing table names in it. As pointed out, I had a single table called Averages2 and a column in that table called [Value]. So, GilesWalker in your formula where you reference ALL([date]) I do not believe that was equivalent to what I was doing if you are not getting the expected result. Probably has to do with your data model being different than my very simple single table data model. Same basic premise should apply ubt would need more information on your model to get specific.

 

 

  • konstantinos's avatar
    konstantinos
    Memorable Member
    10 years ago

    Greg_Deckler Sorry I wasn't reffering to your formula..but mostly on GilesWalker formula on ALL([Date]) that seems to be a column ( due to [ ] ) but not sure if is in the same table as the [Sequence] column, since we only see dates consolidated with train ID...

    • GilesWalker's avatar
      GilesWalker
      Skilled Sharer
      10 years ago

      konstantinos Greg_Deckler - thanks for the replies, I am out of the office today (public holiday), I will try what you said with the ALL(table). I will send through a better example of the table.

       

      Thanks again for all the help.

       

      Giles

      • GilesWalker's avatar
        GilesWalker
        Skilled Sharer
        10 years ago

        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