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)
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 Cost After Cost 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 "1" on the last combination of Part & DateTime for each specific day. (See table above with Desired Result column that I manually populated.)
