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 AnonymousI am bit confused. Can you post some example how do you want to see the data. I know, you have mentioned in first post. But, it will be great if you post example with the last posted data.
rocky09 HI
my main table is
d, 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 i ahve created measure from above table : Measure name is Max ID
MaxID = MAx('eta requisition_life_cycles'[requisition_event_id])
then i created a visualization with measure max id and drag requisition_id in clustered column chart i get data as
MaxID requisition_id 1 99 4 100 5 101 4 102 5 103 4 104 5 105 4 106 5 107 5 108 4 109 5 110 5 111 4 112 5 113 5 114 5 115 5 116 5 117 but i need data as
Max ID Count of requisition_id 1 35 2 32 3 14 4 129 5 229 6 17 7 117
