Forum Discussion
joshua1990
Post Prodigy
Post ProdigyCalculated 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 | 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
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 )
2 Replies
CNENFRNLCommunity Champion
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
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 )