Forum Discussion
Aggregate a distinct user count by first purchase date grouping
Microsoft EmployeeHi jdixon41,
1. You have to change the relationships. Activate another one.
2. 30 days, 60 days has a measure each.
3. There is an error in your expected output. The Purchased Month 2 of August should be 1 due to the sales 2004.
Purchased Month 1 =
SUMX (
ADDCOLUMNS (
'Sales Orders',
"days", 'Sales Orders'[Sales Order Date] - RELATED ( Users[Approved On Date] )
),
IF ( [days] >= 0 && [days] <= 30, 1, 0 )
)Purchased Month 2 =
SUMX (
ADDCOLUMNS (
'Sales Orders',
"days", 'Sales Orders'[Sales Order Date] - RELATED ( Users[Approved On Date] )
),
IF ( [days] > 30 && [days] <= 60, 1, 0 )
)
Best Regards!
Dale
Hi v-jiascu-msft,
I only wanted to count the first purchase for each user. Not all the subsequent purchases. So the idea of the report is to provide insight into how long it takes Users to make their first purchase once approved. We've made some changes to our discounting efforts and we want to see if those 'first purchase' rates are slowing down.
That's why the SalesKey 2004 shouldn't be counted. Nor should SalesKey 2002 (because there is an error in the data).
The way I wanted to approach this problem is envision the final table (referenced in my image on my last post), and then do the DAX functions to fetch the results I want. I don't think that approach is correct because I couldn't apply the functions I wanted to this imaginary filtered table that joins information across two related ones. To be clear I didn't want to create a new table in my model, I wanted to nest CALCULATETABLE, FILTER, SUMMARIZE, ADDCOLUMNS, etc. to get this information together before doing something like COUNTROWS.
I'm very new to DAX and this problem feels very difficult. Adding to all this is my model is in SSAS and we don't want to compound a bunch of one-off columns or tables for every report we are asked to do.
Is the only solution to continually add columns to the model that will only be used by single reports? (This problem becomes straightforward if I add a new column to my Users table called, "FirstPurchaseDate".) I'd like to learn how to solve this purely with DAX instead of relying on adding columns for every hiccup I have with a report request.
-JD