Forum Discussion
Microsoft EmployeeSub-grouping table and conditional column populated from one column of a specific row
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!
3 Replies
v-yuta-msftCommunity Support
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.
beeisabelmUserType 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?