Selecting oldest n records across multiple teams

[PaulSGodden]

Have a set of data as follows:

Team	Project	Owner	Max Age
Project	New Website	John Smith	50
Project	New Website	Clare Jones	40
Project	Office Exit	Clare Jones	50
HR	New Starters	Lucy Baxter	10
IT	New Website	Michael Williams	9
IT	Laptop Upgrade	Sarah Christie	20
HR	Laptop Upgrade	Sophie Adams	4

Am to display only the oldest (Max Age) value for each team. The result should be:

Project	New Website	John Smith	50
Project	Office Exit	Clare Jones	50
HR	New Starters	Lucy Baxter	10
IT	Laptop Upgrade	Sarah Christie	20

If it can only bring back 1 item per team then I'll live with it but ideally the top n items as often projects have multiple actions assigned on the same day

[PaulSGodden]

Both were columns. Now have (not sure how. probably a typo) the Max check and Filter columns in and working.

However:

The Filter has brought back "2" as a result. Have checked and there are two different actions logged to the same person on the same project.

These are open actions and behind the example data I sent are 00's of of Closed records. Example.

Project	Owner	Max Age	Max Age	Max Check	Filter
Project	New Website	John Smith	50	50	1
Project	New Website	Clare Jones	40	50	0
Project	Office Exit	Clare Jones	50	50	1
HR	New Starters	Lucy Baxter	10	10	1
IT	New Website	Michael Williams	9	50	0
IT	Laptop Upgrade	Sarah Christie	20	50	0
HR	Laptop Upgrade	Sophie Adams	4	4	4

Turn the filter on and it doesn't bring back any results for IT.

Thanks again

[fhill]

The duplicate rows are SUM'ing the Checks together when you have duplicates.  Click your visualization column 'values' (all that are numbers) and the Down Arrow and change them to 'Dont' Summarize'.  See if that helps?

[PaulSGodden]

The filter value is now "1" but it is only bringing back 1 record. Both the records (different actions) are still open so should be listed

[PaulSGodden]

Looking at the table (as it was before I started) it is only bring back 1 record for this example. When I look at the source data both actions are open

[fhill]

The inherint function of a Table is to summarize data.  If you can't add a new column (Ticket # / Indicent # / etc) that has something unique in it, maybe you should include a 'Ticket Count' column showing that "IT" / "Laptop Upgrade" / "Sarah" has 3 tickets open... ** Keep in mind, it will only shows 3 if ALL 3 are at MAX Age....   

There are also ways you can RANK your data, to create a new 'index like' column where you can have "IT" "Laptop Upgrade" for "Sara" ticket # 1 and another line for Ticket # 2.  If you want to go that way, please mark this chain's solution, and open a new reqeust with the full scope of what's neeeded.  

Thank You,

[PaulSGodden]

I have an ID column that is unique to each record

[PaulSGodden · Accepted solution]

Added that in and it works. Thanks again

[fhill]

This can be combined into one Calcualted Column, I just have them seperate below to show my process steps...

1. Check for Max Age by Team

Max Check = CALCULATE( MAX(Age[Max Age]), FILTER(ALL(Age), Age[Team] = EARLIER(Age[Team])))

2. Add a Filter Column

Filter = if ( Age[Max Check] = Age[Max Age], 1, 0)

3. Use the Filter column to only show the rows you want...

[PaulSGodden]

Thanks for the response. All went well until ....

EARLIER(Age[Team])))

Get the following message:

A single value for column 'Team' in table 'Age' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result. 

[fhill]

Did you create "new Measure" or "new Coulmn"  ?