How to show top 1 [Item] with the highest [Price] per [Category] when a slicer [Status] is applied?

[windylee]

I want to display the data using "Table" visual to show the Top 1 [Item] with the highest [Price] per [Category] when a slicer [Status] is applied.

Here is my data source table:

Category	Item	Price $	Status
Snacks	Peanuts	10	Active
Snacks	Chips	20	Inactive
Snacks	Candy	15	Pending
Drinks	Coke	30	Active
Drinks	Coffee	25	Inactive
Drinks	Tea	10	Inactive
Fruits	Apple	10	Inactive
Fruits	Banana	15	Active
Fruits	Orange	20	Pending
Fruits	Peach	25	Pending

I want the "Top 1" column can be updated when the slicer is filtered by different values. For example:

I tried to create a measure with the below code but it remains unchanged (it will not update according to different status)

_Measure = 
var _max = MAXX(FILTER(ALL('Sheet1'),[Category]=MAX('Sheet1'[Category])),[Price]) 
return CALCULATE(MAX('Sheet1'[Item]),FILTER(ALL('Sheet1'),[Category]=MAX('Sheet1'[Category]) &&[Price]=_max))

Please advise. Thank you very much!

[vicky_ · Accepted solution]

You will need to add a calculate parameter to get the status to filter

Something like the following:

Measure = 
var selectedStatus = ALLSELECTED('Sheet 1'[Status])
var maxPrices = CALCULATE(MAX('Sheet 1'[Price $]), 'Sheet 1'[Status] in selectedStatus, REMOVEFILTERS('Sheet 1'[Item], 'Sheet 1'[Price $]))
var maxItem =  CALCULATE(MAX('Sheet 1'[Item]), 'Sheet 1'[Price $] = maxPrices)
RETURN maxItem

Here's a good guide to what the ALLSELECTED does - https://www.sqlbi.com/articles/introducing-allselected-in-dax/

[windylee]

Thank you so much for your help!