topic Highest value of a duplicate in DAX Commands and Tips

Highest value of a duplicate

Lm2712 — Thu, 06 Apr 2023 19:11:36 GMT

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?

thanks

Re: Highest value of a duplicate

tamerj1 — Thu, 06 Apr 2023 19:24:40 GMT

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 ) )

Re: Highest value of a duplicate

Ajendra — Thu, 06 Apr 2023 19:45:59 GMT

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