Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
PaulSGodden
Advocate I
Advocate I

Selecting oldest n records across multiple teams

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:

 

IDProjectOwnerMax AgeMax AgeMax CheckFilter
1ProjectNew WebsiteJohn Smith50501
30ProjectNew WebsiteClare Jones

40

50

0

2ProjectOffice ExitClare Jones50501

5

HR

New StartersLucy Baxter10101
45ITNew WebsiteMichael Williams9500
33ITLaptop UpgradeSarah Christie20500
101HRLaptop UpgradeSophie Adams441
  • 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

6 REPLIES 6
Anonymous
Not applicable

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
Super User
Super User

@PaulSGodden 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @ryan_mayu 

It's my raw data

The expected output is:

 

TeamProjectOwnerMax Age
ProjectNew WebsiteJohn Smith50
ProjectOffice ExitClare Jones50

HR

New StartersLucy Baxter10
ITLaptop UpgradeSarah Christie20

 

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

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

1.PNG

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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

@PaulSGodden 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.