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.
I have two tables - Customer and Product. Customer table has a FirstSaleDate column showing the first time a sale was made to a particular customer regardless of the product and Product has FirstProductSale showing the first time a particular product was sold regardless of the customer. I want to create a measure that will provide me with a field (ideally in the Product table) showing when a customer first bought a particular product.
My DAX skills are lacklustre and I'm not sure where to begin. Appreciate any help, thanks.
Solved! Go to Solution.
Hi, @hcr4
I briefly simulated some data that I hope will fit your situation.
Column:
first bought =
LOOKUPVALUE (
Customer[FirstSaleDate],
'Product'[FirstProductSale], [FirstProductSale]
)
Measure:
first bought M =
LOOKUPVALUE (
Customer[FirstSaleDate],
'Product'[FirstProductSale], SELECTEDVALUE('Product'[FirstProductSale])
)
LOOKUPVALUE: https://docs.microsoft.com/dax/lookupvalue-function-dax
Please see the attachment for details.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @hcr4
I briefly simulated some data that I hope will fit your situation.
Column:
first bought =
LOOKUPVALUE (
Customer[FirstSaleDate],
'Product'[FirstProductSale], [FirstProductSale]
)
Measure:
first bought M =
LOOKUPVALUE (
Customer[FirstSaleDate],
'Product'[FirstProductSale], SELECTEDVALUE('Product'[FirstProductSale])
)
LOOKUPVALUE: https://docs.microsoft.com/dax/lookupvalue-function-dax
Please see the attachment for details.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @hcr4
Can you provide some of the example data? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.
Best Regards
Try MINX( 'Order Lines', RELATED(Orders[Order date])
If you also have a Sales table linked to both Product and Customer then MIN(Sales[Order date]) will give the first time a product was ordered. You can put that measure in a table with a Customer and a Product and it will show the first order for that product by that customer
Hi, thanks for the reply. The Customer and Product tables are 'linked' via Orders and Order Lines tables. I.e. Product <--> Order Lines <--> Orders <--> Customers
So in this case I don't think this works.
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 |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |