Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |