Summarize data according to max event_id

[pprakash]

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

[v-lili6-msft · Accepted solution]

hi, @pprakash

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

[edhans]

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])

[pprakash]

@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.

[pprakash]

hi @edhans i think i can get the exact result. as i am new in power bi can u pls help me to create power query for max event id table.

[edhans]

It was in the file I linked to. Click the "Edit Queries" button on the home ribbon.