Forum Discussion
summarize data
Hi Anonymous
From you information, MaxID should be the max requisition_event_id per requisition_id, Count of requisition_id should be the count of requisition_id per MaxID, right?
in my test, [Measure] is the MaxID, i could also create a calculated column max to replace it, then create another column count for Count of requisition_id.
count = CALCULATE(COUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max])) max = CALCULATE(MAX([requisition_event_id,]),ALLEXCEPT(Sheet1,Sheet1[requisition_id,]))
Or if you need distintcount, you can use the following formula
distintcount = CALCULATE(DISTINCTCOUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max]))
Best Regards
Maggie
hi PattemManohar thanks for reply:
Dax formula for Max ID is MaxID = MAx('eta requisition_life_cycles'[requisition_event_id])
and table is
id event_name, sequence required, requires_upload created_at updated_at 1 Created By User | Awaiting HOD's Approval 1 1 0 22-06-2018 7:24 22-06-2018 7:26 2 Approved by HOD | Awaiting Store's Approval 2 1 0 22-06-2018 7:25 22-06-2018 7:26 3 Approved by Store | Awaiting Delivery 3 1 0 22-06-2018 7:26 10-07-2018 6:48 4 Rejected by Store | Requisition Closed 3 0 0 22-06-2018 7:27 06-08-2018 15:02 5 Delivered | Requisition Closed 3 1 0 10-07-2018 6:49 09-08-2018 8:06 6 Rejected by HOD | Requisition Closed 2 0 0 06-08-2018 14:19 06-08-2018 14:19 7 Auto- approved 2 0 0 25-10-2018 16:37 25-10-2018 16:37 sorry the sample of table data is given below
id, requisition_id, requisition_event_id, upload_link, created_by, updated_by, created_at, updated_at 220 99 1 504 504 07-10-2018 20:06 07-10-2018 20:06 221 100 1 596 596 08-10-2018 11:35 08-10-2018 11:35 222 101 1 596 596 08-10-2018 11:40 08-10-2018 11:40 223 102 1 596 596 08-10-2018 11:47 08-10-2018 11:47 224 103 1 419 419 08-10-2018 12:25 08-10-2018 12:25 225 104 1 419 419 08-10-2018 12:27 08-10-2018 12:27 226 105 1 310 310 08-10-2018 14:15 08-10-2018 14:15 227 106 1 310 310 08-10-2018 14:15 08-10-2018 14:15 228 107 1 310 310 08-10-2018 14:16 08-10-2018 14:16 229 108 1 310 310 08-10-2018 14:16 08-10-2018 14:16 230 109 1 310 310 08-10-2018 14:17 08-10-2018 14:17 231 110 1 310 310 08-10-2018 14:17 08-10-2018 14:17 232 111 1 310 310 08-10-2018 14:18 08-10-2018 14:18 233 112 1 310 310 08-10-2018 14:19 08-10-2018 14:19 234 113 1 310 310 08-10-2018 14:20 08-10-2018 14:20 235 114 1 251 251 08-10-2018 15:00 08-10-2018 15:00 or you can refer the final data. its more organized. u can see requisition id is repetitive and i am trying to get only max requisiion_event_id from given requisition_id
id, requisition_id, requisition_event_id, upload_link, created_by, updated_by, created_at, updated_at 220 99 1 504 504 07-10-2018 20:06 07-10-2018 20:06 221 100 1 596 596 08-10-2018 11:35 08-10-2018 11:35 257 100 2 1 1 08-10-2018 20:21 08-10-2018 20:21 323 100 4 32 32 09-10-2018 11:21 09-10-2018 11:21 222 101 1 596 596 08-10-2018 11:40 08-10-2018 11:40 256 101 2 1 1 08-10-2018 20:21 08-10-2018 20:21 320 101 3 32 32 09-10-2018 11:20 09-10-2018 11:20 321 101 5 32 32 09-10-2018 11:20 09-10-2018 11:20 223 102 1 596 596 08-10-2018 11:47 08-10-2018 11:47 255 102 2 1 1 08-10-2018 20:21 08-10-2018 20:21 319 102 4 32 32 09-10-2018 11:20 09-10-2018 11:20 224 103 1 419 419 08-10-2018 12:25 08-10-2018 12:25 341 103 2 424 424 09-10-2018 16:29 09-10-2018 16:29 353 103 3 32 32 09-10-2018 17:20 09-10-2018 17:20 369 103 5 606 606 10-10-2018 10:20 10-10-2018 10:20 225 104 1 419 419 08-10-2018 12:27 08-10-2018 12:27 254 104 2 1 1 08-10-2018 20:20 08-10-2018 20:20 318 104 3 32 32 09-10-2018 11:19 09-10-2018 11:19