Max Rank

[Willatsch]

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

[GaryGiles · Accepted solution]

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)

[Willatsch]

Thank you very much!!

[swuehl · Accepted solution]

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 )

[Willatsch]

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.