cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

 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.

6 REPLIES 6
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

Proud to be a Super User!

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

Super User

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 =
return FILTER(tbl,NOT ISBLANK([check1]))``````

pls see the attachment below

Proud to be a Super User!

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)

Super User

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors