CODEWITHSUNDEEP
powerbidax
user16116406
user16116406

Reputation: 3

Lookup function in Dax

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 !!

Upvotes: 0

Views: 725

Answers (2)

Vojtěch Šíma
Vojtěch Šíma

Reputation: 106

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.

See the solution

If you experience any issues, please post your exact error messages.

Upvotes: 0

user8078111
user8078111

Reputation: 468

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.

Upvotes: 0

Related Questions