Analyzing the Problem

You need to retrieve the output from the previous step based on the current step's input. Specifically, for step number 80, the output must match the input of 90, which comes from the output of step 70.

To achieve this in DAX, you can leverage functions that allow you to reference previous rows or steps effectively. This typically involves the use of LOOKUPVALUE, FILTER, or similar functions that allow you to create a relationship between the steps based on their order.

DAX Solution

Here is a sample DAX formula that can accomplish your goal. This formula will create a calculated column that retrieves the output from the previous step based on the current step's input.

PreviousStepOutput = 
VAR CurrentStep = 'Table'[Step]
VAR PreviousStep = CurrentStep - 10 -- Example assuming each step is 10 units apart
RETURN
    LOOKUPVALUE(
        'Table'[Output], 
        'Table'[Step], 
        PreviousStep
    )

Explanation

• Variables:

  • CurrentStep holds the value of the current row's step.
  • PreviousStep calculates the step number you're interested in (assuming each step is 10 units apart, adjust accordingly).

• LOOKUPVALUE: This function retrieves the 'Output' from the previous step where the 'Step' matches PreviousStep.

Best Practices

1. Use Variables: Utilizing variables like CurrentStep and PreviousStep enhances readability and performance.
2. Testing: Validate the DAX formula by creating a sample table to ensure it retrieves outputs correctly for various steps.
3. Documentation: Maintain clear documentation of any formulas you create for future reference and collaboration.

Conclusion

This DAX approach efficiently retrieves previous step outputs based on the current input conditions. If you require more complex interactions or additional scenarios, consider exploring Enterprise DNA’s resources for DAX courses, which can enhance your understanding of data analysis techniques using appropriate language.