Lookup nth item in a column

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,236
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top