Selecting oldest n records across multiple teams

[PaulSGodden]

Following on from last weeks challenge (Selecting oldest n records across multiple teams) this is still not working as required.

Set up the Max check and Filter fields as advised (thanks @fhill) and the table no looks like:

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

• Filter on "1" and there are no records for IT as the record with the Max check value is closed and this visual only looks for open records
• There is an ID field for each record.

Thanks in advance

[v-shex-msft]

HI @PaulSGodden, 

You can try to use the following measure formula if helps:

Measure =
VAR curr =
    MAX ( Table[Age] )
VAR _max =
    CALCULATE (
        MAX ( Table[Age] ),
        FILTER ( ALLSELECTED ( Table ), [Filter] = 1 ),
        VALUES ( Table[Owner] )
    )
RETURN
    IF ( curr = _max, 1 )

Regards,
Xiaoxin Sheng

[ryan_mayu]

@PaulSGodden 

not clear about your request. Is this your rawdata? what's the expected output?

[PaulSGodden]

Thanks @ryan_mayu 

It's my raw data

The expected output is:

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

Typo in the original table corrected above

Trying to show the oldest (MaxAge) open project for each team

If there are joint oldest projects for a particular team then I would like it to show both

[ryan_mayu]

@PaulSGodden 

you can create a column

check = 
VAR _max=CALCULATE(max('Table'[Max Age.1]),ALLEXCEPT('Table','Table'[Project]))
return if (_max='Table'[Max Age.1],1)

or create a new table

Table 2 = 
VAR tbl=ADDCOLUMNS('Table',"check1",if('Table'[Max Age.1]=CALCULATE(max('Table'[Max Age.1]),ALLEXCEPT('Table','Table'[Project])),1))
return FILTER(tbl,NOT ISBLANK([check1]))

pls see the attachment below

[PaulSGodden]

@ryan_mayu 

Thanks for this. Unfortunately it doesn't completely work if you have Max check values of 50, 50 and 4 for the last 3 records (indicating that there are older records that have already been closed)

All advice gratefully received

[ryan_mayu]

@PaulSGodden 

not clear about this. I provided the solution based on your expected ouptut. Could you please explain more and update the expected output?