PowerPivot multiple columns approximate match

Question

I want to find out the Member_Status on the date that a Product was purchased.

Table A contains 3 fields: Member_Name, Member_Status, Change_Date (this is the date the member started to have this status).

Table B contains 3 fields: Member_Name, Product_Purchased, Purchase_Date.

Both tables have multiple records for each member (e.g. a member may have had many different statuses over the years and a member may have purchased many different products on different days) so I cannot use a standard PowerPivot relationship.

I think I need to do an indirect lookup (look up the Purchase_Date from Table B and find the closest earlier matching Change_Date from Table A FOR the MEMBER that matches in both tables and find the correct Member_Status field for each record in Table B. but I have not been able to figure out a formula in PowerPivot to achieve this.

Any suggestions?

Here is a sample data file: http://www.files.com/shared/5460044b6d805/HowToRelateTwoTablesWithNonUniqueRecords.zip

Kerry, could you please post some sample data / link to your workbook? — Petr Havlik, Nov 08 '14 at 09:48
can you put it on Dropbox and share public link? or http://www.files.com/ — Petr Havlik, Nov 09 '14 at 16:06
Here is the file: http://www.files.com/shared/5460044b6d805/HowToRelateTwoTablesWithNonUniqueRecords.zip — Kerry, Nov 10 '14 at 00:20
I can see you have found the answer already. Doesn't look very intuitive, yet shows the power of DAX language :) — Petr Havlik, Nov 18 '14 at 07:30

score 1 · Answer 1 · answered Nov 16 '14 at 05:27

I found a great blog on this topic at http://javierguillen.wordpress.com/2012/08/05/approximate-match-with-multiple-criteria-in-dax-part-ii/ and figure out the solution. Here is the formula that worked for me. =calculate( LASTNONBLANK(Table2[Member_Status],1), FILTER( Table2, Table2[Date]=CALCULATE(LASTNONBLANK(Table2[Date],1),FILTER(Table2,Table2[Member_Name]=Table1[Member_Name] && Table2[Date]<=Table1[Purchase_Date]) )&& Table2[Member_Name]=Table1[Member_Name]))

PowerPivot multiple columns approximate match

1 Answers1