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

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

Added that in and it works. Thanks again

View solution in original post

10 REPLIES 10
PaulSGodden
Advocate I
Advocate I

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

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!




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

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

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!




I have an ID column that is unique to each record

Added that in and it works. Thanks again

fhill
Resident Rockstar
Resident Rockstar

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!




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. 

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!




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.