Forum Discussion

noyrotbart's avatar
noyrotbart
Frequent Visitor
8 years ago
Solved

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 use...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

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

noyrotbart's avatar
noyrotbart
Frequent Visitor
8 years ago

It 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!