Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
User | Count |
---|---|
136 | |
70 | |
69 | |
54 | |
52 |
User | Count |
---|---|
207 | |
94 | |
64 | |
61 | |
57 |