Highest value of a duplicate

[Lm2712]

Hello,

I am new to Power Bi and am trying to find the highest step used for duplicating item IDs. Below is the data I am working with. 

Item.  Step. 
A.       1

A.       2

A.       3

A.       4

B.       1

B.       2

B.       3

in this dataset the "item" data is repeated as the items move through different steps. I am only interested in using data from the last step. This is what I would like to filter the table down to

item.  Step. 
A.       4

B.       3

Although similar posts exists, these data are different because the item column and the step column both have repeats information. I need to identify the highest step for every single unique item. An additional challenge is the maximum number of steps for different items varies.  

how would I go about this?

similar to: http://community.powerbi.com/t5/DAX-Commands-and-Tips/Highest-Value-of-duplicate/m-p/1689664

thanks

[Ajendra · Accepted solution]

I hope this would work for you!!

Last Step =

VAR _Iteam = MAX(FactTable[Item])

VAR _Teable = FILTER(FactTable,FactTable[Item]=_Iteam)

VAR _MaxVal = MAXX(_Teable,FactTable[Step])

return

_MaxVal

Demo Data

OUTPUT TABLE

[tamerj1]

Hi @Lm2712 

Place the following measure in the filter pane of your visual, select "is not blank" then apply the filter 

FilterMeasure =
VAR MaxStep =
CALCULATE (
MAX ( 'Table'[Step] ),
ALLSELECTED ( 'Table' ),
VALUES ( 'Table'[Item] )
)
RETURN
COUNTROWS ( FILTER ( 'Table', 'Table'[Step] = MaxStep ) )