Results 1 to 4 of 4

Thread: Lookup nth item in a column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Lookup nth item in a column

    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?
    Sincerely, Mike Girvin

  2. #2
    New Member
    Join Date
    Sep 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup nth item in a column

    Quote Originally Posted by mgirvin View Post
    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?

  3. #3
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    234
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup nth item in a column

    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 by macfuller; May 13th, 2018 at 11:07 AM.

  4. #4
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Lookup nth item in a column

    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.
    Sincerely, Mike Girvin

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •