Forum Discussion
Question about lookup function in PBI
Hi everyone, i have a question about lookup in PBI, please help
i am working on a PBI project related to customer product penetration.
Below are three tables.
The first table represents customers who purchased our products. (it has duplicated customer ID because they purchased in different date).
The 2nd table represents what we believe total addressable market is for each customer.
The 3rd table is what i want to see ( end result)
i want the total addressable market to be on the 1st table. however, because it has duplicated customer ID, my lookupvalue formula will make duplicated total addressable market numbers. i can do average or minimal, but i wonder if there are other options.
1st table
| Customer ID | Order Date | Purchase Qty |
| 1 | 1/1/2021 | 18 |
| 1 | 1/15/2021 | 5 |
| 2 | 3/4/2021 | 12 |
| 2 | 4/5/2021 | 22 |
| 3 | 5/6/2021 | 16 |
| 3 | 1/2/2021 | 5 |
| 4 | 5/7/2021 | 12 |
| 4 | 9/4/2021 | 9 |
| 5 | 8/1/2021 | 5 |
| 5 | 5/8/2021 | 7 |
The 2nd table
| Customer ID | Total Addressable market |
| 1 | 200 |
| 2 | 400 |
| 3 | 300 |
| 4 | 323 |
| 5 | 233 |
End Result
| Customer ID | Order Date | Purchase Qty | Total Addressable market |
| 1 | 1/1/2021 | 18 | 200 |
| 1 | 1/15/2021 | 5 | |
| 2 | 3/4/2021 | 12 | 400 |
| 2 | 4/5/2021 | 22 | |
| 3 | 5/6/2021 | 16 | 300 |
| 3 | 1/2/2021 | 5 | |
| 4 | 5/7/2021 | 12 | 323 |
| 4 | 9/4/2021 | 9 | |
| 5 | 8/1/2021 | 5 | 233 |
| 5 | 5/8/2021 | 7 |
leilei787 Try something like this:
Total Addressable market column = VAR __Customer = [Customer ID] VAR __Date = [Order Date] VAR __MinDate = MINX(FILTER('1st Table',[Customer ID] = __Customer),[Order Date]) RETURN IF(__Date = __MinDate,MAXX(FILTER('2nd Table',[Customer ID] = __Customer),[Total Addressable market]),BLANK())
1 Reply
leilei787 Try something like this:
Total Addressable market column = VAR __Customer = [Customer ID] VAR __Date = [Order Date] VAR __MinDate = MINX(FILTER('1st Table',[Customer ID] = __Customer),[Order Date]) RETURN IF(__Date = __MinDate,MAXX(FILTER('2nd Table',[Customer ID] = __Customer),[Total Addressable market]),BLANK())