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.
ORIGINAL POST
Hi There,
I have a table of my customers and their purchase activities broken down by date (Customer ID, Date, Order ID, Source).
I would like to create a measure that can return the first purchase source for my customers.
The CALCULATE / MIN functions allows me to find the first purchase date, but I am unable to use it to return the sale Source.
-------
UPDATED POST
Hi @az38 @sanalytics ,
Aplogies I am new to the forums.
When posting this original post I tried to simplify my requests and data. I have applied @az38 formula as follows but got an error. I must also clarify that the data I have is actually from Google Analytics for users sessions and not customer sales data. My aim is to be able to find the source of the users first session, which is also the customer's source should the user convert into a customer. I am also aiming to find the customer's first sale source, which in this case would be the source of the session in which the customer made their first purchase.
The following suggested formula by @az38 did not work
Field Notes
sessionCount: session squence. So for each user (i.e. sl_userId) will have sessionCount starting from 1 to the number of unique sessions they have made. i.e. Someone visits site for one time they will have a sessionCount= 1 under all of their activities in table. In they last session (n), sessionCount will be n for all of their activities in that last session. Sessions are identified by sl_sessionId.
sl_hitOrder: similar to sessionCount, each activity within a session will be lablelled by a hit order, starting from 1 to n which is the last activitiy in the session. Each session activitiy will have a unique row in data table (Users table).
Is First Session: A conditional column based on sessionCount=1
source: Source of session (i.e. Google, Direct, Facebook, etc.)
pagePath: activity page path on website that Google Analytics is tracking
Solved! Go to Solution.
try
First Session Source =
var FirstPurchaseDate = calculate(MIN('Users'[sl_timeStamp]),ALLEXCEPT('Users','Users'[User]))
return calculate(FIRSTNONBLANK('Users'[source],1),FILTER(ALL('Users'),'Users'[User]=SELECTEDVALUE('Users'[User]) && 'Users'[sl_timeStamp]=FirstPurchaseDate))
do not hesitate to give a kudo to useful posts and mark solutions as solution
Hey @syldia
Can you attach a dummy data and your expected output.It will help us to solve..
Regards,
Snandy
hi @syldia
try a measure in your customer table
FirstPurchaseSource =
var FirstPurchaseDate = calculate(MIN('purchase activities'[Date]))
return calculate(FIRSTNONBLANK('purchase activities'[Source];1);'purchase activities'[Date]=FirstPurchaseDate)
do not hesitate to give a kudo to useful posts and mark solutions as solution
I just added it, got the following error
The syntax for ';' is incorrect. (DAX(var FirstPurchaseDate = calculate(MIN('Users'[sl_timeStamp]))return calculate(FIRSTNONBLANK('Users'[source];1);'Users'[sl_timeStamp]=FirstPurchaseDate))).
Thanks
Formula is working now. However as you can see from visual below it is not delivering the correct results.
try
First Session Source =
var FirstPurchaseDate = calculate(MIN('Users'[sl_timeStamp]),ALLEXCEPT('Users','Users'[User]))
return calculate(FIRSTNONBLANK('Users'[source],1),FILTER(ALL('Users'),'Users'[User]=SELECTEDVALUE('Users'[User]) && 'Users'[sl_timeStamp]=FirstPurchaseDate))
do not hesitate to give a kudo to useful posts and mark solutions as solution
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |