Answered by:
using LOOKUPVALUEneed 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
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.
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 rowbyrow 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."
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.
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 rowbyrow 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."
Thursday, December 18, 2014 5:56 PM 
Best teacherThursday, 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 Edited by Michael Amadi Monday, December 22, 2014 10:52 AM
Monday, December 22, 2014 10:30 AM