topic Re: Calculated Column Lookup next greater value in DAX Commands and Tips

Calculated Column Lookup next greater value

joshua1990 — Sun, 19 Jun 2022 11:47:57 GMT

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

Re: Calculated Column Lookup next greater value

tamerj1 — Sun, 19 Jun 2022 13:04:44 GMT

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 )

Re: Calculated Column Lookup next greater value

CNENFRNL — Sun, 19 Jun 2022 16:53:39 GMT

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,