Report level filter by count of values group by max

[pprakash]

i need to create a visual in power bi. I have sql query 

select requisition_id, MAX(requisition_event_id) from requisition_life_cycles
group by requisition_id having MAX(requisition_event_id)=1;

how can I create a table in power bi using given conditions?

[Stachu]

I don't get the question - the SQL query returns a table right? you want to show all the rows in the table visual?
or do you have a table without the conditions and want to apply them in the visual?

[pprakash]

hi @Stachu thanks for reply. yes second one is correct. i have tables without conditions and want to get data as i am getting it through this query.

[Stachu]

can you share anonymised sample from the input table and the expected output?

[pprakash]

@Stachu    Input table

ID	requisition_id	requisition_event_id	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
370	104	4	32	32	10-10-2018 10:58	10-10-2018 10:58
226	105	1	310	310	08-10-2018 14:15	08-10-2018 14:15
253	105	2	1	1	08-10-2018 20:19	08-10-2018 20:19

OutPut Data

requisition_id	MAX requisition_event_id
99	1
100	4
101	5
102	4
103	3
104	4

[Stachu · Accepted solution]

the simplest approach is to create a measure

MaxId = MAX('Input table'[requisition_event_id])

then drag requisition_id to rows (the Table visual will group them automatically)

and drag the measure to values - it evaluates MAX in the given filter context, wo it will do it for each id

the total (you can disable it if needed) will be the max of all events