Lookup next state based on current state

[joshua1990]

Hi experts!

I have a table that shows me the current working step for each work order:

Order Nr	Step		Article
100	A1		A
101	B2		A

This table is linked to a dimensional table that contains a unique list of all articles.

This unique list is linked to another table, that shows me for each article all possible steps:

Article	Sequence	Step
A	1	A1
A	2	B1
A	4	B2
A	8	C3

Now I would like to calculated in the first table (calculated column) the next step for each work order, based on this logic, based on the sequence.

As you can see the sequence number is not continous filled. There are missing numbers.

How can I determine the next step for each work order?

[v-jayw-msft · Accepted solution]

Hi @joshua1990 ,

Please refer this formula.

Column = 
var seq = CALCULATE(MAX(TableB[Sequence]),FILTER(TableB,TableB[Article]=TableA[Article]&&TableB[Step]=TableA[Step]))
var nextseq = CALCULATE(MIN(TableB[Sequence]),FILTER(TableB,TableB[Article]=TableA[Article]&&TableB[Sequence]>seq))
return
CALCULATE(MAX(TableB[Step]),FILTER(TableB,TableB[Article]=TableA[Article]&&TableB[Sequence]=nextseq))

Best Regards,

Jay

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Next Step CC =
VAR currentarticle = Data[Article]
VAR currentstep = Data[Step]
VAR currentsequence =
    LOOKUPVALUE (
        DimTable[Sequence],
        DimTable[Article], currentarticle,
        DimTable[Step], currentstep
    )
VAR nextsequesnce =
    MINX (
        FILTER (
            ALL ( DimTable ),
            DimTable[Article] = currentarticle
                && DimTable[Sequence] > currentsequence
        ),
        DimTable[Sequence]
    )
RETURN
    SUMMARIZE (
        FILTER (
            ALL ( DimTable ),
            DimTable[Article] = currentarticle
                && DimTable[Sequence] = nextsequesnce
        ),
        DimTable[Step]
    )

[tamerj1]

Hi @joshua1990 

you can try

Next Step =
VAR CurrentSequence =
    RELATED ( DimTable[Sequence] )
VAR NextStepSequence =
    MINX (
        FILTER ( DimTable, DimTable[Sequence] > CurrentSequence ),
        DimTable[Sequence]
    )
RETURN
    MAXX (
        FILTER ( DimTable, DimTable[Sequence] = NextStepSequence ),
        DimTable[Step]
    )