locked
using LOOKUPVALUE--need urgent help please RRS feed

  • Question

  • Hello,

    what im trying to do is grab the first date when someone bought a product. I have 2 tables. 1 where my products are registered with product ID and the other table shows when the item was bought. What im trying to do is show in the first table only the date where item was first bought. Im using lookupvalue and gives me an error because I have repeated itemsID  in the dates list.

    How could I only grab the first date??

    Thank you

    Thursday, December 18, 2014 5:00 PM

Answers

  • Try this (if it doesn't work, read this and ask again):

    FirstDatePurchased=
    CALCULATE(
        MIN( <fact table>[<date column>] )
        , FILTER(
            <fact table>
            , <fact table>[itemsID] = EARLIER( <product dimension>[itemsID] )
        )
    )

    Hopefully this works on the first try for you.

    • Proposed as answer by greggyb Thursday, December 18, 2014 6:03 PM
    • Marked as answer by recio Monday, December 22, 2014 2:14 PM
    Thursday, December 18, 2014 5:27 PM
  • Power Pivot Pro and "The Italians" have been the richest sources for me.

    What that measure does is find the smallest value in your fact table - MIN( ... ), but this function is evaluated in a special context.

    CALCULATE() allows us to change the context that we evaluate a function in. What we're doing is changing the context so that we evaluate MIN() only on the rows in the table specified by FILTER().

    FILTER() takes a table, and then iterates row-by-row over that table, performing the logical test that we specify. It returns only those rows that have a true value for that test. The test is checking that the [itemsID] on the current row (current row in our iteration using FILTER()) is equal to the [itemsID] from the row of the product dimension that this whole expression (CALCULATE( MIN(...), FILTER(...))) is being evaluated for. EARLIER() says, "I know my current row refers to the current row in my iteration through FILTER(), but there is also a current row in the table where this calculated column is defined - give me the latter meaning of current row."

    • Proposed as answer by greggyb Thursday, December 18, 2014 6:03 PM
    • Marked as answer by recio Monday, December 22, 2014 2:15 PM
    Thursday, December 18, 2014 5:56 PM

All replies

  • Try this (if it doesn't work, read this and ask again):

    FirstDatePurchased=
    CALCULATE(
        MIN( <fact table>[<date column>] )
        , FILTER(
            <fact table>
            , <fact table>[itemsID] = EARLIER( <product dimension>[itemsID] )
        )
    )

    Hopefully this works on the first try for you.

    • Proposed as answer by greggyb Thursday, December 18, 2014 6:03 PM
    • Marked as answer by recio Monday, December 22, 2014 2:14 PM
    Thursday, December 18, 2014 5:27 PM
  • I have no idea of what you did there but it worked hahha thank you so much! I only have 2 days working with powerpivot. Do you recommend any places to learn?
    Thursday, December 18, 2014 5:34 PM
  • Power Pivot Pro and "The Italians" have been the richest sources for me.

    What that measure does is find the smallest value in your fact table - MIN( ... ), but this function is evaluated in a special context.

    CALCULATE() allows us to change the context that we evaluate a function in. What we're doing is changing the context so that we evaluate MIN() only on the rows in the table specified by FILTER().

    FILTER() takes a table, and then iterates row-by-row over that table, performing the logical test that we specify. It returns only those rows that have a true value for that test. The test is checking that the [itemsID] on the current row (current row in our iteration using FILTER()) is equal to the [itemsID] from the row of the product dimension that this whole expression (CALCULATE( MIN(...), FILTER(...))) is being evaluated for. EARLIER() says, "I know my current row refers to the current row in my iteration through FILTER(), but there is also a current row in the table where this calculated column is defined - give me the latter meaning of current row."

    • Proposed as answer by greggyb Thursday, December 18, 2014 6:03 PM
    • Marked as answer by recio Monday, December 22, 2014 2:15 PM
    Thursday, December 18, 2014 5:56 PM
  • Best teacher
    Thursday, December 18, 2014 6:02 PM
  • Hi Recio,

    If any post in this thread has answered your question, please use the 'Mark as answer' link to mark it as an answer :)


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    Monday, December 22, 2014 10:30 AM