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
can you put it on Dropbox and share public link? or files.com — Petr Havlik
I can see you have found the answer already. Doesn't look very intuitive, yet shows the power of DAX language :) — Petr Havlik

Kerry Kerry · Accepted Answer · 2014-11-16T05:27:04

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 Answers