Forum Discussion
mgirvin
Advocate I
Advocate ILookup nth item in column
Dear Team, In Excel we can lookup a value using INDEX and MATCH function, where the MATCH function returns the row number or relative position of an item in a list. How do we do this is DAX? Spec...
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.
Greg_Deckler
Community Champion
Community ChampionEh, maybe, see this post:
https://stackoverflow.com/questions/38599531/dax-create-dynamic-index-column
mgirvinAdvocate I
Advocate IThank you very much for the help Greg : )