Lookup nth item in a 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:

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

 

[bkjohn2016]

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]

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?

 

[mgirvin]

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.