# using LOOKUPVALUE--need urgent help please

• ### 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

• 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 Thursday, December 18, 2014 6:03 PM
• Marked as answer by 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 Thursday, December 18, 2014 6:03 PM
• Marked as answer by 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 Thursday, December 18, 2014 6:03 PM
• Marked as answer by 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 Thursday, December 18, 2014 6:03 PM
• Marked as answer by 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,