March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
Thanks in advance
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
not clear about your request. Is this your rawdata? what's the expected output?
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
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 =
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
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)
All advice gratefully received
not clear about this. I provided the solution based on your expected ouptut. Could you please explain more and update the expected output?
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
86 | |
82 | |
76 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |