Forum Discussion
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.
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 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)
10 Replies
- Greg_Deckler
Community Champion
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/339586.
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 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)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.
