Vlookup with duplicate values in both columns

[emdnz]

Hey team, I feel the answer to my question does sit somewhere within this forum but my capability of understandig DAX limits my ability to combine several solutions into one. So here goes:

I have some logic setup in an excel sheet that I'm trying to replicate into PBI but I'm struggling. Right now I have two tables that I'm trying to do a lookup between, initially I used "lookupvalue" but that doesn't like duplicate values. Now I'm using "firstnonblank" where I copied some formula from a forum that seems to work but I don't actually understand, so if you know of a solution please treat me like I'm a 5 year old because I'd prefer understanding the code rather than simply replicate.

Table 1: lead data

Basically individual records with contact details and a creation date. Email address is what I do to do my "vlookup" but creation date is also important as I want the most recent record in case of the email address occurring more than once. 

Table 2: Product purchases

These are date stamped purchase records against email addresses. 

What I'm trying to achieve is a column in my lead data that gives me any desired column from the product purchases table for an email match for both the most recent lead and the most recent purchase. In this case I'm after product category for example.

This is admittedly step 1 in a bigger string of verification so bonus points if it's possible to create 1 column for the penultimate result I'm after:

I need a count of matches where the purchase date occurred AFTER the creation date as I'm trying to prove the indirect effect of some of our marketing campaigns with leads who don't DIRECTLY engage with us through submitting leads, do end up purchasing indirectly. 

I'll need several columns of table 2 such as category, sub category, and the actual days between lead creation and purchase so ideally looking for a vlookup esque function that can be applied to any column of choosing. 

If anyone can help me out with this that'd be greatly appreciated. 

Table 1

Email	Creation date	Desired output
john@gmail.com	2020	A
tim@gmail.com	2019	B
frank@gmail.com	2019	null
john@gmail.com	2019	null

Table 2

Email	Purchase date	Product category
john@gmail.com	2020	A
tim@gmail.com	2019	B
john@gmail.com	2015	C

[shep123]

By using FIRSTNONBLANK() you essentially turn this into a vlookup. In this scenario if you don't use it DAS essentially returns a list and doesn't know how to handle it resulting in the error. When a vlookup is used in excel it basically find the corresponding matching value and stops. When you use firstnonblank you are kinda telling the formula to stop searching once it finds that first value that isn't blank. Disclaimer that isn't true technically speaking but in practice it is. Does that make sense?

To get the next step you will need to put a filter in your lookup formula that adds that condition. If you send your current formula I can help you configure it to your ask.

[emdnz]

Hi, thanks so much for your explanation. I've been playing around a bit and trying to verify and the formula does appear to work the way it's intended. However, I'm running into a bit of a duplication issue. The current formula is: (pardon the formatting)

Look up column = CALCULATE(FIRSTNONBLANK('Products'[Brand], TRUE () ),

FILTER (

Products,

Products[Primary E-mail] = 'Look up table'[Email]

&& 'Look up table'[Email] = EARLIER( ('Look up table'[Email])

)))

As a next step I've set up the following formula. This calculates the number of days between lead creation and product purchase, a follow up formula looks at whether the number is >0 (ensuring the purchase occurred after lead creation) and I count those as attribution sales. However! Within our product structure it does happen regularly that a single email address will occur multiple times in the list, so currently the same email address can be counted as a sale in several rows that contain the same email address, even though they only tie back to 1 purchase. Is there a way to alter the first firstnonblank formula to STOP running as soon as there's a hit? Rather than count every single instance of email match? It still wouldn't be perfect but I'd rather have accurate data that I can assume is higher, than inflated numbers.

Product Datedif customer purchase and lead creation = DATEDIFF('Look up table'[Created On],'Look up table'[Product purchase date],DAY)