Getting the calculated Max value of each group.

[pprakash]

Hi,

I am working on a table and need data according to count of req_event_id, group by req_id and max created_date.

i did some calculation but unable to get only max value. 

ApprovedByHod = CALCULATE(count('eta requisition_life_cycles'[id]),'eta requisition_life_cycles'[req_event_id]=2 )

ID	req_id	req_event_id	created_by	updated_by	created_date	updated_date
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

[v-chuncz-msft · Accepted solution]

@pprakash,

You may add a calculated table first.

Table =
SUMMARIZE (
    Table1,
    Table1[req_id],
    "req_event_id", MAXX ( TOPN ( 1, Table1, Table1[updated_date], DESC ), Table1[req_event_id] )
)

[smpa01]

 Is this what you are looking for  @pprakash

[pprakash]

hey sorry i could'nt make you understand. i need total count of req_event ids based upon( last created at and group by req_id)

something like last req_event id based upon last updated according to grouping of req_id

req_id     Req_event id      Last updated

100         1                          .............

101         2                          .............

102        2                         ............

103      3                          ............

then 

total count of req_event_id ie total count of 1 then total count of 2 and total count of 3. 

please excuse my english. it will be very helpful if you suggest any solution.

[pprakash]

hey @smpa01 sorry i could'nt make you understand. i need total count of req_event ids based upon( last created at and group by req_id)

something like last req_event id based upon last updated according to grouping of req_id

req_id     Req_event id      Last updated

100         1                          .............

101         2                          .............

102        2                         ............

103      3                          ............

then 

total count of req_event_id ie total count of 1 then total count of 2 and total count of 3. 

please excuse my english. it will be very helpful if you suggest any solution.