Lookup the earliest instance of an entry in a table with multiple values

[clint_powell]

I have a table structured simliarly to the format below. Each entry has a unique entry ID and entry date, but the same user ID has multiple entries over time. I am trying to write a lookup where the User ID is in a new table, with an added column that returns the Entry ID associated with the earliest Entry Date. This would be similar to a MINIFS statement in Excel. Any help is hugely appreciate!!!

Entry ID	User ID	Entry Date		User ID	First Entry ID
1	1101	1/19/2024		1101	1
2	1102	1/20/2024		1102	2
3	1103	1/21/2024		1103	3
4	1101	1/22/2024		1104	7
5	1102	1/23/2024
6	1103	1/24/2024
7	1104	1/25/2024
8	1101	1/26/2024
9	1103	1/27/2024
10	1103	1/28/2024

[ryan_mayu · Accepted solution]

@clint_powell 

you can try 

Column = CALCULATE(min('Table'[Entry ID]),RELATEDTABLE('Table'))


or


Column 2 = 
VAR _date=CALCULATE(min('Table'[Entry Date ]),RELATEDTABLE('Table'))
return maxx(FILTER('Table','Table'[Entry Date ]=_date),'Table'[Entry ID])

pls see the attachment below

[v-jianpeng-msft · Accepted solution]

Hi, @clint_powell 

I'm happy to answer your questions. Here I offer my solution，First of all, we filter out the smallest entry date by MINX function. To obtain the _mindate, we use the calculate function to find the corresponding Entry ID.

The test data is as follows:

Table 1 (Tabelle1)：

Sheet1：

We can use the following dax formula:

First Entry ID = 
var _userid ='Sheet1'[User ID]
var _mindate = MINX(FILTER('Table 1 (Tabelle1)','Table 1 (Tabelle1)'[User ID]=_userid),'Table 1 (Tabelle1)'[Entry Date ])
VAR _id = CALCULATE(SUMX('Table 1 (Tabelle1)','Table 1 (Tabelle1)'[Entry ID]),'Table 1 (Tabelle1)'[Entry Date ]=_mindate)
RETURN _id

The screenshot of the formula is below:

The relationship between the tables is as follows:

The results are as follows:

When we change the date, the latest first entry ID will be automatically updated：

The latest results are as follows:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[ryan_mayu · Accepted solution]

@clint_powell 

you can try 

Column = CALCULATE(min('Table'[Entry ID]),RELATEDTABLE('Table'))


or


Column 2 = 
VAR _date=CALCULATE(min('Table'[Entry Date ]),RELATEDTABLE('Table'))
return maxx(FILTER('Table','Table'[Entry Date ]=_date),'Table'[Entry ID])

pls see the attachment below