Check if row is smallest value from previous rows

[FIJT]

Hello,

I apologize in advance, if my question doesn't make sense. It's a bit difficult to describe.

I have been trying to create a function in Power Bi, which numbers solutions based on if it is the lowest energy cost based on the investment amount. The lowest energy cost (Y axis) with the given investment (X-axis) is plotted with a line.

I have created it on excel which I use as data source, by sorting the table based on energy cost and referring to the previous investment cells to check if it is the lowest. But I can't find a way to create a similar funcition in Power Bi.

So if anyone can find a way to check, if the investment is the smallest value from previous rows with the current energy cost in Power Bi and number the results would be a great help!

Thanks for reading 🙂 

[FIJT]

I think the EARLIER function is the key.

The best I've tried, is after sorting the table by energy

=IF( Table[Investment]<MINX(FILTER(Table,EARLIER(Table[Investment])),Table[Investment]),[Energy],BLANK())

[Greg_Deckler]

@FIJT I was thinking:

Column = 
  VAR __OrderNumber = [Order number.]
  VAR __CurrentInvestment = [Investment]
  VAR __MinPrevInvestment = MINX(FILTER('Table',[Order number.]<__OrderNumber),[Investment])
RETURN
  MIN(__CurrentInvestment, __MinPrevInvestment)

[FIJT]

The only columns that we have to work with in Power Bi are Investment and Energy.

The column I am trying to create, checks if the current row of Investment is smaller than the previous rows.  In excel the column (Smallest) also returns the energy value if the investment was smallest. 

[Greg_Deckler]

@FIJT Then what exactly defines "previous"? I recommend an Index column in PQ.

[FIJT]

As the values are sorted in descending order (largest to smallest) in Power Bi, aren't the values that are higher (larger) considered previous from lower (smaller) values?

This seems like a easy function, that is somehow impossibly difficult in Power Bi.

[Greg_Deckler · Accepted solution]

@FIJT Well, if that's what you are saying to be true, then we'll go with that, here is the measure using Energy as the sorting mechanism. Just remember, sort order is not guaranteed for just about any DAX except CONCATENATEX of all things. So, just because something appears sorted in the table doesn't mean it will come back that way when you run it through a DAX formula. But if Energy is always increasing, you can do this:

Column = 
  VAR __Energy = [Energy cost]
  VAR __CurrentInvestment = [Investment]
  VAR __MinPrevInvestment = MINX(FILTER('Table',[Energy cost]<__Energy),[Investment])
RETURN
  MIN(__CurrentInvestment, __MinPrevInvestment)

[FIJT]

@Greg_Deckler This is the solution! Thank you 🙂 

I used the formula to create (with energy sorted ascending): 

= VAR __Energy = [Energy Cost]

VAR __CurrentInvestment = [Investment]

VAR __MinPrevInvestment = MINX(FILTER('Table',[Energy]<__Energy),[Investment])

RETURN

IF(__CurrentInvestment<__MinPrevInvestment, [Energy Cost], BLANK())

Do you know if it is possible to have this calculated column updated, based on values filtered in the report?

Thanks again @Greg_Deckler 

[Greg_Deckler]

@FIJT No, not a column, you would need a measure like:

= VAR __Energy = MAX([Energy Cost])
VAR __CurrentInvestment = MAX([Investment])
VAR __MinPrevInvestment = MINX(FILTER('Table',[Energy]<__Energy),[Investment])
RETURN
IF(__CurrentInvestment<__MinPrevInvestment, [Energy Cost], BLANK())

If you put Investment and Energy in a table visual along with this measure it should provide you the same information the column does and be dynamic.

[FIJT]

Thank you so much for all the replies!

I used this to create multiple measures (don't know if it was necessary) ->

Measure 1 = MAX(Table[Investment])

Measure 2 = MAX(Table[Energy Cost])

Measure 3 =MINX(FILTER(allselected('Table'),[Energy Cost]<[Measure 2])),[Investment])

Measure 4= IF([Measure 1]<[Measure 3],[Measure 2],IF([Measure 3]<1,[Measure 2],BLANK()))

Measure 4 gives me the correct value and reacts to filters applied on the page. (Had to add the last IF, because it would skip the 1st value for some reason)

[Greg_Deckler]

@FIJT See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous