Forum Discussion
Summarize data according to max event_id
HI
my main table is
| 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 |
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 column 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 is max requisition_event_id of a group of requisition_id
| Max ID | Count of requisition_id |
| 1 | 35 |
| 2 | 32 |
| 3 | 14 |
| 4 | 129 |
| 5 | 229 |
| 6 | 17 |
| 7 | 117 |
hi, Anonymous
After my test, you may try to this way as below:
Step1: Add a calculate column
Use EARLIER Function to find MAX("requisition_event_id") for each "requisition_id"
MaxEventID = CALCULATE ( MAX ( 'eta requisition_life_cycles'[requisition_event_id] ), FILTER ( 'eta requisition_life_cycles', 'eta requisition_life_cycles'[requisition_id] = EARLIER ( 'eta requisition_life_cycles'[requisition_id] ) ) )Step2: Add a calculate measure
Add a measure that Count (requisition_id)
Count of requisition_id = CALCULATE(COUNTA('eta requisition_life_cycles'[requisition_id]))Step3: Create a visual
Drag the field MaxEventID and measure Count of requisition_id into visual
Best Regards,
Lin
6 Replies
- edhans
Community Champion
You don't need to use the MAX() function. Just drag the Requestion Event ID to a table and then in the dropdown in the Values section on the right, tell it "Don't summarize"
Then use the following measure to count the number of Requestion IDs for each Event ID.
Requistion ID Count = COUNT('eta requisition_life_cycles'[requisition_id])edhans as you can see there are n numbers of requisition_event_id for one requisition_id. i need max event id for requisition_id then i need count of requisition_id for that max event ids
- edhans
Ok. See if this file is what you need. I used Power Query to determine the Max Event ID for each type of requestion ID in a separate table that is NOT loaded. Then I merged that table back to the original table, then used a simple COUNT() measure.
v-lili6-msftCommunity Support
hi, Anonymous
After my test, you may try to this way as below:
Step1: Add a calculate column
Use EARLIER Function to find MAX("requisition_event_id") for each "requisition_id"
MaxEventID = CALCULATE ( MAX ( 'eta requisition_life_cycles'[requisition_event_id] ), FILTER ( 'eta requisition_life_cycles', 'eta requisition_life_cycles'[requisition_id] = EARLIER ( 'eta requisition_life_cycles'[requisition_id] ) ) )Step2: Add a calculate measure
Add a measure that Count (requisition_id)
Count of requisition_id = CALCULATE(COUNTA('eta requisition_life_cycles'[requisition_id]))Step3: Create a visual
Drag the field MaxEventID and measure Count of requisition_id into visual
Best Regards,
Lin
