cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FIJT
Regular Visitor

Check if row is smallest value from previous rows

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.

FIJT_2-1632386292954.png

 

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.

FIJT_1-1632385995586.png

 

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 🙂 

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

10 REPLIES 10
FIJT
Regular Visitor

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

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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. 

 

FIJT_0-1632394691693.png

 

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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.

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@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 

@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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!