Dynamically find next min value compared to previous value stored in a measure

[Anonymous]

First of all, pardon me if the subject title is a little misleading but I found it hard to describe the problem effectively as a title.

First off, some background information:

I have a table with an index column and some values that's filtered by a slicer. This means that the index values might not come in a normal order, and can e.g look like below:

Table1:

Index	Value
1	Test value 1
3	Test value 3
4	Test value 4
7	Test value 7
20	Test value 20

My objective based on this as of right now, is to have 5 measures (in this situation) that each holds "the next minimum value based on the previous measure". However, when attempting this (see formula below) I'm getting a "blank" value (checked when inserting to a card).

min val 1 = min('Table1'[Index])

min val 2 = calculate(min('Table1'[Index]);FILTER('Table1';'Table1'[Index]>[min val 1]))

Substituting the "[min val 1]" measure part from the "min val 2" measure statement gives the correct result of 3, however using the measure just leaves it blank.

Isn't it possible to create/use/have such dependencies ?

[d_gosbell · Accepted solution]

You can do this, the issue is that inside your Filter() call the value for the [min val 1] measure is recalculated for each row. This effectively filters out every row as the min value for a single row is the value of the index for that row.

The simple fix is to use a variable to capture the value of the measure outside of the filter.

min val 2 = 
var _min_val_1 = [min val 1]
return calculate(min('Table1'[Index]),FILTER('Table1','Table1'[Index]> _min_val_1))