Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

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 ?

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

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

View solution in original post

1 REPLY 1
d_gosbell
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.