Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
noyrotbart
Frequent Visitor

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?

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @noyrotbart,

I try to reproduce your scenario and get expected result. I create the following sample table.

1.PNG

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)

2.PNG

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])

3.png


Please feel free to ask if you have any question.

Best Regards,
Angelia

View solution in original post

2 REPLIES 2
v-huizhn-msft
Employee
Employee

Hi @noyrotbart,

I try to reproduce your scenario and get expected result. I create the following sample table.

1.PNG

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)

2.PNG

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])

3.png


Please feel free to ask if you have any question.

Best Regards,
Angelia

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.