Calculated Column Lookup next greater value

[joshua1990]

Hi experts!

I have a dimensional table that shows me the number of working steps per Article and a different table that contains the current state per article.

Dimensional table

Article	Step	Value
AB	1	5
AB	2	4
AB	3	8
AT	1	8
AT	2	7

 Value Table

Article	Current Step	Next Step Value
AB	2	8
AB	1	4

Both tables are linked Many-to-Many.

What I would like to get is the column "Next Step Value" that shows me the Value for the next step.

How is this possible using DAX? 

If the current step is the last step, I would like to get "Current Step" value

[CNENFRNL]

As composite keys are not supposed in either PBI or SSAS tabular model, such a rookie's question is, in fact, fairly tricky and demanding.

A showcase of powerful Excel worksheet formula,

[tamerj1 · Accepted solution]

Hi @joshua1990 

please use

Next Step Value =
VAR CurrentStep = 'Value Table'[Current Step]
VAR RelatedStepsTable =
    RELATEDTABLE ( 'Dimensional table' )
VAR CurrentStepTable =
    FILTER ( RelatedStepsTable, 'Dimensional table'[Step] = CurrentStep )
VAR CurrentValue =
    MAXX ( CurrentStepTable, 'Dimensional table'[Value] )
VAR NextStepsTable =
    FILTER ( RelatedStepsTable, 'Dimensional table'[Step] > CurrentStep )
VAR NextStep =
    MINX ( NextStepsTable, 'Dimensional table'[Step] )
VAR NextValue =
    MAXX (
        FILTER ( NextStepsTable, 'Dimensional table'[Step] = NextStep ),
        'Dimensional table'[Step]
    )
RETURN
    IF ( ISBLANK ( CurrentValue ), NextValue, CurrentValue )