Lookup function in Dax

0
votes

How can I create a DAX (calculated field) to perform a lookup from table 1 to table 2 in Power pivot as below?

  1. Customer Table (Dimension)
Customer Key FirstPurchaseDate
10000 9/7/2018
10001 28/7/2018
10002 9/9/2021
  1. 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 !!

1
powerbidax
Can you show in image? It is quite hard to see how the data look like, probably also what is the expected output - Kin Siang
Do the tables have a relationship? What is the reason for duplicating an existing column to another table? - Alexis Olson
Oh, Sales table didn't have, and will not have a physical First purchase date, I want to do it in measure (calculated field) as reference for another measure. - user16116406

1 Answers

0
votes

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])

Output and Input

Also, you use the LOOKUPVALUE function for checking the conditions from multiple tables.