cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GilesWalker Skilled Sharer
Skilled Sharer

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 examplTrain id.PNG

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: 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]))

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

13 REPLIES 13
Highlighted
Super User IV
Super User IV

Re: Set a filter based on MAX

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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

GilesWalker Skilled Sharer
Skilled Sharer

Re: Set a filter based on MAX

@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:


Max.PNG


However when you create the custom column all values are the same "1".:


Ismax filter.PNG


If I enter the [Sequence] data into the table and select dont summarize you can see what happens:

All together.PNG


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


konstantinos Memorable Member
Memorable Member

Re: Set a filter based on MAX

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

Konstantinos Ioannou
Super User IV
Super User IV

Re: Set a filter based on MAX

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.

 

 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

konstantinos Memorable Member
Memorable Member

Re: Set a filter based on MAX

@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...

Konstantinos Ioannou
GilesWalker Skilled Sharer
Skilled Sharer

Re: Set a filter based on MAX

@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 Skilled Sharer
Skilled Sharer

Re: Set a filter based on MAX

@konstantinos @Greg_Deckler Please see the screenshot of the table Train-Movements:

 

 

 

 

 

Train movements.PNG

 

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:

 

Result.PNG

 

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

Super User IV
Super User IV

Re: 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]))

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

GilesWalker Skilled Sharer
Skilled Sharer

Re: Set a filter based on MAX

@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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors