Need help in Lookup with if condition

Anonymous — Mon, 09 Mar 2020 19:45:18 GMT

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

Re: Need help in Lookup with if condition

az38 — Mon, 09 Mar 2020 20:12:27 GMT

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

topic Need help in Lookup with if condition in DAX Commands and Tips

Need help in Lookup with if condition

Re: Need help in Lookup with if condition