Question about lookup function in PBI

leilei787 — Thu, 24 Feb 2022 22:30:46 GMT

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

Re: Question about lookup function in PBI

Greg_Deckler — Thu, 24 Feb 2022 22:44:54 GMT

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

topic Re: Question about lookup function in PBI in DAX Commands and Tips

Question about lookup function in PBI

Re: Question about lookup function in PBI