Lookup nth item in a column

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,212
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:

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

because 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?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

bkjohn2016

New Member
Joined
Sep 9, 2016
Messages
38
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:

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

because 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?
Which position are you looking for? List position in the sales table or the discount table?
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
257
Your formula appears to want to find the number of instances where the discount units are <= the sales quantity, and you'd like to find (say) the 3rd-lowest instance of a sales (or discount) figure? Would there always be at least 3 instances or would we need to trap for categories where the number of instances might be less?

I always find it hard to make the mental shift from Excel formulas to DAX thinking. In this case, instead of looking at how to go to a specific location in a dataset is it more useful in DAX to figure out how to strip away/filter any row that doesn't match what you're looking for so that you're only left with the one you want? What makes the position relevant? Is it sorted in some way? I think it's that underlying logic the DAX would represent rather than position?
 
Last edited:

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,212
Thank you both, bkjohn2016 and macfuller!!

I eventually had to abandon "relative position" and just use Exact Match and LOOKUPVALUE. I converted the approximate match lookup value to an exact match with CALCULATE(MAX(disDiscount[Units]),FILTER(disDiscount,disDiscount[Units]<=fSales[Quanity])) and then used that value inside of LOOKUPVALUE. I also was able to build a relationship once I converted the approximate match to exact match.

Yes, I agree that going from Excel Formula Thinking to Data Model / Columnar Database / DAX thinking is quite a journey : ) In this case, since there is no conceptual "sort" in the Columnar database, it makes relative position a non-concept... and so we have to resort to Exact Match lookup.
 

Forum statistics

Threads
1,089,394
Messages
5,407,983
Members
403,176
Latest member
mehtavish1

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top