Return Value from Another Column

[syldia]

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

[sanalytics]

Hey @syldia 

Can you attach a dummy data and your expected output.It will help us to solve..

Regards,

Snandy

[az38]

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

LinkedIn

[az38]

@syldia 

you missed the closed parenthesis ")" symbol before return.

[syldia]

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

[az38]

@syldia 

replace ";" to "," comma. it's a system localization question

do not hesitate to give a kudo to useful posts and mark solutions as solution

[syldia]

Formula is working now. However as you can see from visual below it is not delivering the correct results. 

[az38 · Accepted solution]

@syldia 

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