Next production order on a machine

[CRN]

Hi all,

I'm trying to somewhat summarize a table with production data. I wan't to find the next production order set to be produced for each machine by due date and status.

I have the following data:

This is what i'm trying to get:

The criteria is as follows for each machine:

Production order with earliest due date and status pending.

If production orders have the same due date I wan't to display the lowest order number.

Thanks in advance!

Note: If necessary, I'll be able to exclude all production orders with status completed in advance of retrieving it.

[johnt75 · Accepted solution]

You could create a measure like 

Is Next Order =
VAR currentOrderNo =
    SELECTEDVALUE ( 'Table'[Order no] )
VAR nextOrderNo =
    SELECTCOLUMNS (
        CALCULATETABLE (
            TOPN ( 1, 'Table', 'Table'[Due date], ASC, 'Table'[Order no], ASC ),
            ALLEXCEPT ( 'Table', 'Table'[Machine] ),
            'Table'[Status] = "Pending"
        ),
        "@val", 'Table'[Order no]
    )
RETURN
    IF ( currentOrderNo = nextOrderNo, 1 )

and then use that as a visual filter on your table to only show when the value is 1

[CRN]

Hi @johnt75 ,

Thank you for your suggestion.

I've tried it out and also tweaked it a little to see if it fixes the results.

It works if i apply a few more filters on the visual. So my initial issue has been resolved.

I tried to add another filter like you did on the status, but they don't seem to work.

Can you help me figure out why? Do I place them wrong?

Is Next Order =
VAR currentOrderNo =
    SELECTEDVALUE ( 'Table'[Order no] )
VAR nextOrderNo =
    SELECTCOLUMNS (
        CALCULATETABLE (
            TOPN ( 1, 'Table', 'Table'[Due date], ASC, 'Table'[Order no], ASC ),
            ALLEXCEPT ( 'Table', 'Table'[Machine] ),
            'Table'[Status] = "Pending",
            'Table'[OperationNo] = 100,
            'Table'[OrderType] = 1
        ),
        "@val", 'Table'[Order no]
    )
RETURN
    IF ( currentOrderNo = nextOrderNo, 1 )

[johnt75]

They seem to be placed correctly. 

What filters are being applied to the visual? 

What exactly is not working ? What are you expecting to see and what are you actually seeing ?

[CRN]

I solved the issue.

Recently another Doc-Type was added to the dataset and I re-categorized them. Had a multiple filters interfering with one another. It works now.

Thank you so much for your help.