Return Value from Another Column

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

First Session Source =

var FirstPurchaseDate = calculate(MIN('Users'[sl_timeStamp])

return calculate(FIRSTNONBLANK('Users'[source];1);'Users'[sl_timeStamp]=FirstPurchaseDate)

 

ERROR MESSAGE

The syntax for 'return' is incorrect. (DAX(var FirstPurchaseDate = calculate(MIN('Users'[sl_timeStamp])return calculate(FIRSTNONBLANK('Users'[source];1);'Users'[sl_timeStamp]=FirstPurchaseDate))).

 

I have already created the following measures that actually work fine with in my model, perhaps you can reuse them in your suggested solution formulas.

 

First Session =

MINX(

    ALLSELECTED('Users'[sl_userId]),

    CALCULATE(MIN('Users'[sl_sessionId]),ALLSELECTED('Users'))

)

 

First Sale Date = CALCULATE (

MIN ( 'Users'[sl_timeStamp] ),

'Users'[Is Sale Temp]=1

)

 

Data Fields

 

 

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

 

Data Sample