Forum Discussion
Ranking
Part Date time Before After DESIRED RESULT a 1/1/2022 9:00:00 AM 0.1 0 a 1/1/2022 9:15:00 AM 0.1 0.7 0 a 1/1/2022 11:00:00 AM 0.7 0.3 0 a 1/1/2022 3:00:00 PM 0...
Anonymous Maybe:
DESIRED RESULT column = VAR __Part = [Part] VAR __Date = [Date] VAR __time = [time] VAR __MaxDate = MAXX(FILTER('Table',[Part] = __Part),[Date]) VAR __MaxTime = MAXX(FILTER('Table',[Part] = __Part && [Date] = __MaxDate),[time]) RETURN IF([Part] = __Part && [Date] = __Date && [time] = __time,1,0)
| Part | Before | After | Date Time | Desired RandRank |
| A | 0.415 | 2/8/19 11:09 | 0 | |
| A | 0 | 2/8/19 11:09 | 0 | |
| A | 0 | 2/8/19 11:09 | 0 | |
| A | 24.1 | 2/8/19 11:09 | 0 | |
| A | 0.065 | 2/8/19 11:09 | 0 | |
| A | 103.5 | 2/8/19 11:09 | 0 | |
| A | 103.5 | 2/8/19 11:09 | 0 | |
| A | 46.5 | 2/8/19 11:09 | 0 | |
| A | 36.5 | 2/8/19 11:09 | 0 | |
| A | 36.5 | 2/8/19 11:09 | 0 | |
| A | 36.5 | 2/8/19 11:09 | 0 | |
| A | 36.5 | 2/8/19 11:09 | 0 | |
| A | 36.5 | 2/8/19 11:12 | 1 | |
| A | 0.415 | 0.49 | 2/13/19 6:27 | 0 |
| A | 103.5 | 76.5 | 2/13/19 6:29 | 0 |
| A | 103.5 | 76.5 | 2/13/19 6:29 | 1 |
| A | 0.065 | 0.0865 | 2/14/19 10:14 | 1 |
| A | 0.49 | 0.5195 | 2/27/19 12:19 | 1 |
| A | 46.5 | 4/18/19 8:41 | 1 | |
| A | 0.5195 | 0.4495 | 6/14/19 15:17 | 1 |
| A | 76.5 | 103.5 | 2/20/20 16:03 | 0 |
| A | 76.5 | 103.5 | 2/20/20 16:03 | 1 |
| A | 24.1 | 38.98 | 6/5/20 14:20 | 1 |
| A | 0.4495 | 0.91 | 5/17/21 10:48 | 1 |
| A | 0.91 | 1.12 | 9/22/21 16:30 | 1 |
| A | 1.12 | 0.4495 | 12/3/21 7:28 | 0 |
| A | 0.4495 | 1.12 | 12/3/21 7:30 | 1 |
| A | 1.12 | 0.4495 | 12/10/21 6:53 | 0 |
| A | 0.4495 | 0.91 | 12/10/21 7:10 | 0 |
| A | 0.91 | 1.12 | 12/10/21 7:12 | 0 |
| A | 1.12 | 0.91 | 12/10/21 7:18 | 0 |
| A | 0.91 | 1.12 | 12/10/21 7:19 | 1 |
| A | 1.12 | 0.4395 | 1/26/22 16:09 | 0 |
| A | 0.4395 | 0.8455 | 1/26/22 16:42 | 0 |
| A | 0.8455 | 0.91 | 1/26/22 16:43 | 0 |
| A | 0.91 | 0.965 | 1/26/22 16:44 | 0 |
| A | 0.965 | 1.02 | 1/26/22 16:45 | 0 |
| A | 1.02 | 1.12 | 1/26/22 16:46 | 1 |
OK, How do I create this desired result? I have many parts and want the desired rank of either "0" or "1". On the last combination of Part & DateTime for each specific day. (See table above with Desired Result column that I manually populated.). I have multiple changes happening each day, but I only want to work with the last one of each day. I will then need to compare the ship date of another table to the closest after date that contains a "1" and then ruturn the text volue of the After field. (Text field type because it is also other changes) from the above table. I thought that I would need the 0's and 1's but if there is an easier way to do this I am more than willing to listen.