Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Have a set of data as follows:
Team | Project | Owner | Max Age |
Project | New Website | John Smith | 50 |
Project | New Website | Clare Jones | 40 |
Project | Office Exit | Clare Jones | 50 |
HR | New Starters | Lucy Baxter | 10 |
IT | New Website | Michael Williams | 9 |
IT | Laptop Upgrade | Sarah Christie | 20 |
HR | Laptop Upgrade | Sophie Adams | 4 |
Am to display only the oldest (Max Age) value for each team. The result should be:
Project | New Website | John Smith | 50 |
Project | Office Exit | Clare Jones | 50 |
HR | New Starters | Lucy Baxter | 10 |
IT | Laptop Upgrade | Sarah Christie | 20 |
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
Solved! Go to Solution.
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.
Project | Owner | Max Age | Max Age | Max Check | Filter |
Project | New Website | John Smith | 50 | 50 | 1 |
Project | New Website | Clare Jones | 40 | 50 | 0 |
Project | Office Exit | Clare Jones | 50 | 50 | 1 |
HR | New Starters | Lucy Baxter | 10 | 10 | 1 |
IT | New Website | Michael Williams | 9 | 50 | 0 |
IT | Laptop Upgrade | Sarah Christie | 20 | 50 | 0 |
HR | Laptop Upgrade | Sophie Adams | 4 | 4 | 4 |
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?
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,
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
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
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...
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" ?
Proud to give back to the community!
Thank You!