How can I create a DAX (calculated field) to perform a lookup from table 1 to table 2 in Power pivot as below?
- Customer Table (Dimension)
| Customer Key | FirstPurchaseDate |
|---|---|
| 10000 | 9/7/2018 |
| 10001 | 28/7/2018 |
| 10002 | 9/9/2021 |
- Sales Table (Fact)
| Customer Key | Order Date | FirstPurchaseDate |
|---|---|---|
| 10000 | 9/7/2019 | |
| 10000 | 28/7/2019 | |
| 10001 | 9/10/2019 | |
| 10003 | 9/10/2019 |
Both tables have customer key as primary key with 1-many relationship. I've try related, relatedtable and lookupvalue but fail.
Thanks a lot for your help !!
32 Answers
You can create a calculated column with the LOOKUPVALUE DAX function in the sales tables, it worked for me. Try the following DAX and if it helps then mark it as the answer.
First Order Date = LOOKUPVALUE(Table1[FPD], Table1[Customer Key], Table2[Cutomer Key])
Also, you use the LOOKUPVALUE function for checking the conditions from multiple tables.
with your sample data, simple RELATED function is all you need, given you have established a relationship Sales:Customer - (Many:One) and made this relationship Active, then with simple FirstPurchaseDate = RELATED(Customer[FirstPurchaseDate]), you have your working solution.
If you experience any issues, please post your exact error messages.
