Forum Discussion

Lm2712's avatar
Lm2712
New Member
3 years ago
Solved

Highest value of a duplicate

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

Duplicates
Filter
Max
value
  • Ajendra's avatar
    Ajendra
    3 years ago

    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

2 Replies

  • Ajendra's avatar
    Ajendra
    Icon for Resolver I rankResolver I
    3 years ago

    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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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