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.
Advocate IO, I added an index to the lookup table. You said "add an idex column in your query", how would I do that? Is there a way to use ADDCOLUMNS to add an index internally in the formula?
Community ChampionIn the Query Editor, click your query, click "Add Column" tab in ribbon and then Index Column.
- mgirvin
Yes, I did add an index column. But I was wondering if there was a way to do it internally in the formula?
- Greg_Deckler
Eh, maybe, see this post:
https://stackoverflow.com/questions/38599531/dax-create-dynamic-index-column
- mgirvin
Thank you very much for the help Greg : )