Forum Discussion
Matrix with Oldest item
- 5 years ago
Hi user900
I got it. Thank you for your reply.😉
-
Create the measure;
Expected = VAR _maxDays = //get the highest Business Days according to 'Table'[Status]& 'Table'[Category]; CALCULATE ( MAX ( 'Table'[Business Days] ), ALLEXCEPT ( 'Table', 'Table'[Status], 'Table'[Category] ) ) RETURN IF ( MAX ( 'Table'[Status] ) = "Cancelled" || MAX ( 'Table'[Status] ) = "Closed", 0, IF ( MAX ( 'Table'[Business Days] ) = _maxDays, 1, 0 )//if 'Table'[Business Days] = the highest Business Days for each Category, then return 1, else 0; )result:
See sample file attached bellow.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.
Thanks again for your time. I used the suggested measure but it didn't seem to give me the result I was expecting based on Active items with the highest # of Business Days. I added a new column to see which items are returning as 1 vs 0. From the sample below you'll see items that are not active are returning 1 based on the suggested solution. All Closed and Cancelled regardless of Category should be 0.
Category Status Business Days Result: Suggested Solution
STU 1 Closed 728 1
A10 1 Closed 599 1
A20 2 Closed 909 1
A26 2 Cancelled 513 1
A29 2 Cancelled 540 1
Then for Active items, I'm looking for the one item in each category that has the highest # of business days. The sample below shows what returned using the suggested solution (but as a newly created column). I'm expecting 1 for item A46 and A90 vs 0 based on these having the highest # of business days in their category.
Item Category Status Business Days Result: Suggested Solution
A46 1 Active 284 0
A90 2 Active 495 0
A103 2 Active 254 0
A104 1 Active 224 0
Hi user900
I've tested with my sample again. and it still works OK. it seems my measure is right...
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.