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
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.
Just to add, would it be possible to do this in the constraint when setting up the data model?