topic Re: Get highest value per group in Developer

Get highest value per group

michaelskj8 — Mon, 08 Oct 2018 11:33:55 GMT

Hi all. Hope you are able to help me.

I have read a similar post about filtering on the highest value per group, but this unfortunately does not solve my issue. Here goes.:

I have a data table like the one below. I need to find the file related to the newest year, and get all years associated with that file filtered so they are the only ones left in the data table.

File name Customer number Year

A 1000 2012

A 1000 2013

A 1000 2014

B 1000 2012

B 1000 2013

B 1000 2014

B 1000 2015

In this case, file "B" is the one i want all the rows for, since 2015 is the newest year. I.e. wanted result is:

B 1000 2012

B 1000 2013

B 1000 2014

B 1000 2015

How do I do this?

PS: I have many thousand rows

BR Michael

Re: Get highest value per group

Greg_Deckler — Mon, 08 Oct 2018 14:59:49 GMT

I'm thinking:

Customer = 
VAR __max = MAXX('Table',[Year])
VAR __filemax = MAXX(FILTER('Table',[Year]=__max),[File name])
VAR __table = FILTER('Table',[File name]=__filemax)
RETURN
__table

Re: Get highest value per group

michaelskj8 — Tue, 09 Oct 2018 07:54:52 GMT

Will give it a shot and come back 🙂

Re: Get highest value per group

michaelskj8 — Tue, 09 Oct 2018 07:58:38 GMT

Do I create a new table or column?