Lookup nth item in column

[mgirvin]

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? Specifically, I have a calculated column and this formula that returns the relative position (or row number) of an item in a column:

=COUNTROWS(FILTER(disDiscount,disDiscount[Units]<=fSales[Quanity]))

For each row in this Calculated Column, that formula is returning a number between 1 and 7, which represents the row in a column that contains the item I want to go and get and bring back to the calculated column.

I can't use a formula like this to lookup the value:

=CALCULATE(MAX(disDiscount[Discount]),FILTER(disDiscount,disDiscount[Units]<=fSales[Quanity]))

becasue the value I am looking up is not always the MAX value. 

I need a lookup formula that return an item in a column based on its position.

What DAX Function can I use to lookup an item in a column based on its position?

[Greg_Deckler · Accepted solution]

Add an Index column in your query?

[Greg_Deckler · Accepted solution]

Add an Index column in your query?

[mgirvin]

That 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 · Accepted solution]

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.

[mgirvin]

O, 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?

[Greg_Deckler]

In 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 : )