
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Max Rank
Hello guys I have a problem with my set expression.
What I have is a set expression to filter from a big data source: Max({1<Customer ={"1"}>*1<Product ={"Laptop"}>}[Price])
This way I can filter the customer I need to show in my analysis with the specific product showing the maximum price that was paid. But what I also want is to show the second highest price that was paid. I know it works with a rank and a number. I just dont know where to put the rank in my syntax. Can somebody help me?
Kind regards
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The FirstSortedValue() function will allow you to specify the position of the sorted values. So, in the expression below, it should return the 2nd highest value.
=FirstSortedValue({1<Customer ={"1"},Product ={"Laptop"}>} Distinct [Price],-[Price],2)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The Max() function also has a second, optional argument to request the nth top ranked value, not only the maximum.
Max({1<Customer ={"1"}>*1<Product ={"Laptop"}>}[Price] , 2 )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The FirstSortedValue() function will allow you to specify the position of the sorted values. So, in the expression below, it should return the 2nd highest value.
=FirstSortedValue({1<Customer ={"1"},Product ={"Laptop"}>} Distinct [Price],-[Price],2)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much!!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The Max() function also has a second, optional argument to request the nth top ranked value, not only the maximum.
Max({1<Customer ={"1"}>*1<Product ={"Laptop"}>}[Price] , 2 )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hallo Swuehl,
I discovered that feature while playing around with the syntax that was given to me by GaryGiles. Both of you helped me a lot to understand the set expression better.
