Forum Discussion
Selecting a value in earliest date.
Suppose I have a table with multiple entries per user (identified by a userID), and assume that each such entry has a meaningful string X.
I'd like to create a calculated row containing for each user, the X for the users first entry.
A starting point I thoght of is TOPN(1,calcaulatetable(table, userID=earllier(userID),Date,ASC) which would give the row needed, but how to extract that string X from that row, I haven't a clue.
Is this the right approach at all?
Hi noyrotbart,
I try to reproduce your scenario and get expected result. I create the following sample table.
I will get the string for earliest date for each username. First, I create a calculate a column to get the rank of date for each username.Rank = RANKX(FILTER(Table3,Table3[username]=EARLIER(Table3[username])),Table3[date],,ASC)
Then you just filter the table to get the rows where rank=1. Please click "New Table" under Modeling on Home page. Type the formula and get expected result.New = SELECTCOLUMNS(FILTER(Table3,Table3[Rank]=1),"username",Table3[username],"String",Table3[String])
Please feel free to ask if you have any question.
Best Regards,
Angelia
2 Replies
v-huizhn-msftMicrosoft Employee
Hi noyrotbart,
I try to reproduce your scenario and get expected result. I create the following sample table.
I will get the string for earliest date for each username. First, I create a calculate a column to get the rank of date for each username.Rank = RANKX(FILTER(Table3,Table3[username]=EARLIER(Table3[username])),Table3[date],,ASC)
Then you just filter the table to get the rows where rank=1. Please click "New Table" under Modeling on Home page. Type the formula and get expected result.New = SELECTCOLUMNS(FILTER(Table3,Table3[Rank]=1),"username",Table3[username],"String",Table3[String])
Please feel free to ask if you have any question.
Best Regards,
AngeliaIt indeed worked beautifully, with the exepction that the values were not unique (namely, there was more than one username/Date combination), for which we broke ties off arbitrarly. Many thanks!
