Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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:

 

IndexValue
1Test value 1
3Test value 3
4Test value 4
7Test value 7
20Test 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 ?

Need Help
  • d_gosbell's avatar
    d_gosbell
    6 years ago

    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))

1 Reply

  • d_gosbell's avatar
    d_gosbell
    Super User
    6 years ago

    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))