Hi all,
I am trying to do a look up to find the number of people who have ordered one product and then gone on to order another.
I have a calculated table with a list of product A and a calculated table with a list of product B with the customer ID's in both table.
However, when I use the LOOKUP function to lookup the order date from the product B table some customers have ordered more than once and therefore I have the error due to there being multiple values.
When I create the table for product B, is there a way for me to add something in so that the table only shows one line per customer based on the latest purchase of product B which the customer made.
Here is the dax I am using to create my product B table.
Product B purchases = CALCULATETABLE (
SELECTCOLUMNS(Sales, "Customer ID",Sales[Customer ID],"Purchase Date", Sales[Purchase Date],"Customer Name", Sales[Customer Name],"Purchase Country", Sales[Country],"Purchase Price", Sales[Price]),
FILTER (
Sales,
Sales[Purchase Date] >= DATE(2020,03,01)
&& Sales[Purchase Date] <= DATE(2021,12,31)
&& NOT(ISBLANK(Sales[Customer ID]))
)
)
I am guessing I need to use the MAX function but I am not sure how or where to add it and if I need anything else such as GROUPBY.
Many thanks
