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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-shex-msft
Community Support
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.
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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