cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Report level filter by count of values group by max

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Report level filter by count of values group by max

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

Re: Report level filter by count of values group by max

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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Report level filter by count of values group by max

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.

Highlighted
Super User II
Super User II

Re: Report level filter by count of values group by max

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Report level filter by count of values group by max

 

@Stachu    Input table

 

IDrequisition_idrequisition_event_idcreated_byupdated_bycreated_atupdated_at
22099150450407-10-2018 20:0607-10-2018 20:06
221100159659608-10-2018 11:3508-10-2018 11:35
25710021108-10-2018 20:2108-10-2018 20:21
3231004323209-10-2018 11:2109-10-2018 11:21
222101159659608-10-2018 11:4008-10-2018 11:40
25610121108-10-2018 20:2108-10-2018 20:21
3201013323209-10-2018 11:2009-10-2018 11:20
3211015323209-10-2018 11:2009-10-2018 11:20
223102159659608-10-2018 11:4708-10-2018 11:47
25510221108-10-2018 20:2108-10-2018 20:21
3191024323209-10-2018 11:2009-10-2018 11:20
224103141941908-10-2018 12:2508-10-2018 12:25
341103242442409-10-2018 16:2909-10-2018 16:29
3531033323209-10-2018 17:2009-10-2018 17:20
369103560660610-10-2018 10:2010-10-2018 10:20
225104141941908-10-2018 12:2708-10-2018 12:27
25410421108-10-2018 20:2008-10-2018 20:20
3181043323209-10-2018 11:1909-10-2018 11:19
3701044323210-10-2018 10:5810-10-2018 10:58
226105131031008-10-2018 14:1508-10-2018 14:15
25310521108-10-2018 20:1908-10-2018 20:19

 

 

OutPut Data

 

requisition_idMAX requisition_event_id
991
1004
1015
1024
1033
1044
Highlighted
Super User II
Super User II

Re: Report level filter by count of values group by max

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Come join us today! Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors