Sub-grouping table and conditional column populated from one column of a specific row

[beeisabelm]

I have a table of user event names and adhoc info; Some of the events have adhoc info that needs to be used to set a conditional column values for all other rows that have same user ID. How do I create a DAX function to dynamically set the column value? 

EventName	UserId	Adhoc1	Adhoc2	UserType	Status
PageLoaded	1		Product1
SubmitButtonClicked	1	NewUser
TransactionCompleted	1
PageLoaded	2		Product1
PageLoaded	3		Product2
SubmitButtonClicked	3	ExistingUser

The desired result

EventName	UserId	Adhoc1	Adhoc2	UserType	Status
PageLoaded	1		Product1	NewUser	Success
SubmitButtonClicked	1	NewUser		NewUser	Success
TransactionCompleted	1			NewUser	Success
PageLoaded	2		Product1	NewUser	Bailed
PageLoaded	3		Product2	ExistingUser	Success
SubmitButtonClicked	3	ExistingUser		ExistingUser	Bailed

Conditional column "UserType" - 

Table.AddColumn(#"Sources", "UserType", each if Text.StartsWith([EventName], "SubmitButtonClicked") then [Adhoc2] else if (Filter(Table, AllExcept[UserId], ??)) else null)

Conditional column "Status" - 

Table.AddColumn(#"Sources", "Status", each if [UserId] == LATER??[UserId] then "Success" else "Bailed")

Thank you for help!

[v-yuta-msft]

@beeisabelm ,

Could you please clarify more details/logic about dax statement below in your custom function?

Filter(Table, AllExcept[UserId], ??)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[beeisabelm]

UserType and Status are conditional columns. The table is pre-sorted by UserId and TimeStamp so the events happened in the correct order. (I ommitted the timestamp column in this sample table)

UserType is determined by Adhoc1 value when EventName is SubmitButtonClicked. I want to populate all other rows under same UserId with same UserType value. Ex. UserId 1 will have all rows with UserType "NewUser". UserId 3 will have all rows with UserType "ExistingUser". Otherwise, null.

Status is Success if the event is not the last event taken by the user. Status is Bailed if it is the last event by this user. Ex. UserId 1 PageLoaded event Status is Success. UserId 2 PageLoaded event Status is Bailed.

I am trying to iterate through the rows with same UserId to populate the conditional column, but not sure how to utilize the keyword "Ealier". Thank you for the help. @v-yuta-msft 

[beeisabelm]

I was able to solve the first question with below DAX and create a new column

UserTypeGenerated = IF(CALCULATE(COUNTROWS(TableName),ALLEXCEPT('TableName',TableName[UserId]),TableName[UserType]="ExistingUser")>0,"ExistingUser","NewUser")

However, I still need help with the second one. @v-yuta-msft  Can you help?