Forum Discussion
Advocate ILookup nth item in column
Add an Index column in your query?
Using your approach of adding an index column, I came up with this solution that works:
=LOOKUPVALUE(disDiscount[Discount],disDiscount[Index],COUNTROWS(FILTER(disDiscount,disDiscount[Units]<=fSales[Quanity])))
Another solution I found in the comments at YouTube is this:
=LOOKUPVALUE(disDiscount[Discount],disDiscount[Units],CALCULATE(MAX(disDiscount[Units]),FILTER(disDiscount,disDiscount[Units]<=fSales[Quanity])))
where the logic is to find max value in first column of lookup table (units) and then use that as an Exact Match in LOOKUPVALUE.
Community ChampionAdd an Index column in your query?
Advocate IThat would work. Thank you, Greg!
But, my question remains: What DAX Function can I use to lookup an item in a column based on its position? If there is not a function like this, it seems strange that such a common function in Excel (INDEX), would not be in DAX.
- Greg_Deckler
Well, I would only say that DAX isn't really geared toward "position" so much. Under the hood things really aren't sorted neatly into ordered columns and rows like a sheet in Excel. You have to filter your way to specific values because there is no reference frame like A12, B13, etc.
- mgirvin
O, yes... That does make perfect sense becasue the Columnar Database stores unique list : )
- mgirvin
Using your approach of adding an index column, I came up with this solution that works:
=LOOKUPVALUE(disDiscount[Discount],disDiscount[Index],COUNTROWS(FILTER(disDiscount,disDiscount[Units]<=fSales[Quanity])))
Another solution I found in the comments at YouTube is this:
=LOOKUPVALUE(disDiscount[Discount],disDiscount[Units],CALCULATE(MAX(disDiscount[Units]),FILTER(disDiscount,disDiscount[Units]<=fSales[Quanity])))
where the logic is to find max value in first column of lookup table (units) and then use that as an Exact Match in LOOKUPVALUE.