Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
@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())
@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())
User | Count |
---|---|
51 | |
36 | |
20 | |
15 | |
13 |
User | Count |
---|---|
94 | |
72 | |
29 | |
22 | |
13 |