Order status, based on parts availability

[ianneg77]

I need help on this one. I am working on a project with my shipping folks. I need to be able to show if an order is complete, based on the lowest common result.

For example, I have an order made up of 3 different parts, each individual part has a stock status, "OK" or "short". If all of the parts in the order have a stock status as OK, the order can be marked as "complete". If any of the 3 parts in the order are "short", the order status needs to be marked as "not complete". I repeat the order needs to be marked "not complete", not the item line. I already have a field I calculate the the correct status for the item line. When I have an order with lots of parts each with their own stock status I can't get it to work correctly.  See screen shot:

This order needs to be marked "NO" in "Order Complete" column since 1 of the 4 items is short, even though the other 3 are "OK". Can't ship until all items are "OK" then order can be marked as complete. HELP!

[v-frfei-msft · Accepted solution]

Hi @ianneg77,

I made one sample for your reference. If the sample data cannot meet your requirement, kindly share your sample data to me.

Measure = var result =CALCULATE(MAX(Table1[RESULT]),ALLEXCEPT(Table1,Table1[or-no]))
return 
IF(result="Short","not complete","complete")

For more details, please check the pbix as attached.

Regards,
Frank

[v-frfei-msft · Accepted solution]

Hi @ianneg77,

I made one sample for your reference. If the sample data cannot meet your requirement, kindly share your sample data to me.

Measure = var result =CALCULATE(MAX(Table1[RESULT]),ALLEXCEPT(Table1,Table1[or-no]))
return 
IF(result="Short","not complete","complete")

For more details, please check the pbix as attached.

Regards,
Frank

[ianneg77 · Accepted solution]

This is great, worked the way i needed it to. Thanks to you and the other folks who contributed potential solutions. This worked with what I was trying to do best. This community is indispensable.

[dedelman_clng]

You should be able to do something along the lines of

IsComplete = IF ( MAXX(Parts, Parts[StockStatus]) = "OK", TRUE(), FALSE() )

Hope this helps

David

ETA: You should consider moving the order status field outside of the detail area (to the order, rather than the part) of the visual so there is only one value.

[Minseven]

just an idea:

why not create another table, add conditionnal column "If short = 1 else 0" then group by Order number and aggregate by taking the MAX of your conditional column.

Now go back to your order table and Merge (Many to one) and expand the previously created conditionnal column