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|
|2||Project||Office Exit||Clare Jones||50||50||1|
|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
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 )
It's my raw data
The expected output is:
|Project||New Website||John Smith||50|
|Project||Office Exit||Clare Jones||50|
|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
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?
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.