topic Re: Display only rows that contain the max value in Getting Data In

Display only rows that contain the max value

obrienk — Thu, 05 Jun 2014 12:47:09 GMT

I am attempting to use a pivot grid to display items in a grid that contain the max value of a column

Example:

Users	Orders	VersionNumber
1	2	1
2	4	3
3	5	4
4	3	5
5	6	5

I only want to display the rows that contain the max VersionNumber.
When doing something like this through the search command, I would use eventstats to store the max value and then test each row against that.

Example:
| eventstats max(VersionNumber) as Big | where VersionNumber = Big

I would have thought that the filter in the pivot table would have enabled me to do this but I have not been able to see a solution. Can anyone help?

Thanks

Re: Display only rows that contain the max value

obrienk — Thu, 05 Jun 2014 12:51:45 GMT

Just to add, would it be possible to do this in the constraint when setting up the data model?

Re: Display only rows that contain the max value

obrienk — Mon, 09 Jun 2014 09:06:02 GMT

As an update to this question, I resolved the issue by moving away from the pivot table and using the eventstats in each query to limit the results to the max VersionNumber. This is now working.