Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Calculated Column Lookup next greater value

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 ...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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 )
CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
4 years ago

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,