Max Op Number

[Anonymous]

I am working on an operations history table.  The key field is work order.  Every work order has multiple operations.  For example:
Work Order      Operation
1801                  10
1801                  20
1802                  10

1802                  20

1802                  30

How do i find the maximun operation for each work order?  The closest i've come is:

Column 2 =

var CurrentWO = Max('pub op_hist'[op_wo_op])

Return

MAXX(

Filter('pub op_hist','pub op_hist'[op_wo_nbr]),

'pub op_hist'[op_wo_op]

)

However that only gives me the maximun operation number for all work orders in the table.
Your help is very much appreciated

[parry2k · Accepted solution]

@Anonymous add this measure

Max Op = CALCULATE ( MAX ( Table[Op Number] ), ALLEXCEPT ( Table, Table[Work Order] ) ) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

I added the following as both a column and measure.  It gives the highest op number for the entire table (2000) intead of the highest for each work order:

Last Op =
     Calculate(
          Max('pub Op_Max'[op_wo_op)),
          AllExecpt('pub Op_Max','pub Op_Max[op_wo_nbr])
)

I suspect it is the ALLEXECPT command but I don't know what to replace it with

[parry2k]

@Anonymous it will work if you view the data in a table visual, put order number and this new measure and check the result, if not then share how you are using this measure.

[Anonymous]

I am using a table visual.  Unfortunately I cannot upload the table b/c of IT setting at corporate.  In fact I'm using my private computer to type this.  It is a simple table:

Work Order    Measure
16122              2000
161844            2000
162450            2000

The two tables are linked by a separate filed wo_lot in an one-to-many relationlship.  I don't use wo_nbr since each work order can have several lots

[parry2k]

@Anonymous the solution was provided based on the original post, if there are more tables and relationships you need to provide relevant information. having multiple tables with relationship and how tables are connected can lead to different results, same DAX expression cannot fit for all

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡