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'm trying to determine if there is a way to write a formula on my Customer table to look for the first time a product category was purchased. For example, Jim Smith first purchased product A on 1/1/2010 but I'm interested in knowing IF AND WHEN Jim Smith purchased Product C. I've got the first time purchase column completed:
FirstTimePurchase = CALCULATE(MIN('Operations: Orders'[Order Date]),ALLEXCEPT('Operations: Orders',Customer[CustomerID]))
But I don't know how to add the filter to then determine MIN order with Subscripton Type = "Autorefill".
HELP??
Hi, @ciken
Based on your description, I created data to reproduce your scenario.
Table:
You may create two measures as below.
FirstTimePurchaseA autorefill =
CALCULATE(
MIN('Operations: Orders'[Order Date]),
FILTER(
ALLSELECTED('Operations: Orders'),
'Operations: Orders'[CustomerID] = SELECTEDVALUE('Operations: Orders'[CustomerID])&&
'Operations: Orders'[Subscription type] = "Autorefill"
)
)
PurchaseC autorefill Date =
var result =
CONCATENATEX(
FILTER(
ALLSELECTED('Operations: Orders'),
'Operations: Orders'[CustomerID] = SELECTEDVALUE('Operations: Orders'[CustomerID])&&
'Operations: Orders'[Product] = "C"&&
'Operations: Orders'[Subscription type] = "Autorefill"
),
'Operations: Orders'[Order Date],
","
)
return
IF(
ISBLANK(result),
"no purchase Product C",
result
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-alq-msft ,
The problem lies in that my subscription type is in a different table than my order date. My order date is in 'Operations: Orders' and my subscription type (and all product data) is in a table called 'Operations: Orders Shopping Cart'. When I try to write either of the codes below it is not allowing me to pull data from a second table into the measurement.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |