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?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.