Reply
PaulSGodden
Advocate I
Advocate I
Partially syndicated - Outbound

Selecting oldest n records across multiple teams

Have a set of data as follows:

 

TeamProjectOwnerMax Age
ProjectNew WebsiteJohn Smith50
ProjectNew WebsiteClare Jones

40

ProjectOffice ExitClare Jones50

HR

New StartersLucy Baxter10
ITNew WebsiteMichael Williams9
ITLaptop UpgradeSarah Christie20
HRLaptop UpgradeSophie Adams4

 

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

 

ProjectNew WebsiteJohn Smith50
ProjectOffice ExitClare Jones

50

HR

New StartersLucy Baxter10
ITLaptop UpgradeSarah Christie20

 

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

1 ACCEPTED SOLUTION

Syndicated - Outbound

Added that in and it works. Thanks again

View solution in original post

10 REPLIES 10
PaulSGodden
Advocate I
Advocate I

Syndicated - Outbound

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.

 

ProjectOwnerMax AgeMax AgeMax CheckFilter
ProjectNew WebsiteJohn Smith50501
ProjectNew WebsiteClare Jones

40

50

0

ProjectOffice ExitClare Jones50501

HR

New StartersLucy Baxter10101
ITNew WebsiteMichael Williams9500
ITLaptop UpgradeSarah Christie20500
HRLaptop UpgradeSophie Adams444

 

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

 

Thanks again

Syndicated - Outbound

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?




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Syndicated - Outbound

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

Syndicated - Outbound

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

Syndicated - Outbound

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,




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Syndicated - Outbound

I have an ID column that is unique to each record

Syndicated - Outbound

Added that in and it works. Thanks again

fhill
Resident Rockstar
Resident Rockstar

Syndicated - Outbound

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...

fhill_0-1627050036850.png

 

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Syndicated - Outbound

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. 

Syndicated - Outbound

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)