Need help in Lookup with if condition

[Anonymous]

I would like to use lookup with if condition, but there are some duplicates value in the user table. If two UserID are assigned to that ID, use the one who was assigned for the most days.

User Table 

UserID	ID	StartDate	EndDate
A	1	31-03-2005	24-05-2005
B	2	31-03-2005	19-04-2005
C	3	09-08-2005	19-09-2005
E	1	27-01-2006	21-04-2006
F	2	21-02-2006	01-06-2006
G	3	21-02-2006
D	4	13-09-2005

ID Table;

ID	Expected UserID
1	E
2	F
3	G
4	D

[az38 · Accepted solution]

Hi @Anonymous 

try a measure

Measure = 
var _maxPeriod = CALCULATE(MAXX('User Table',DATEDIFF('User Table'[StartDate],IF(ISBLANK('User Table'[EndDate]),TODAY(),'User Table'[EndDate]),DAY)),FILTER(ALL('User Table'),'User Table'[ID]=SELECTEDVALUE('ID Table'[ID])))
RETURN
CALCULATE(FIRSTNONBLANK('User Table'[UserID],1),FILTER(ALL('User Table'),'User Table'[ID]=SELECTEDVALUE('ID Table'[ID]) && DATEDIFF('User Table'[StartDate],IF(ISBLANK('User Table'[EndDate]),TODAY(),'User Table'[EndDate]),DAY)=_maxPeriod)),