Need help in Lookup and Condition

[Anonymous]

I would like to use lookup with a condition, but there is some duplicates value in the ID table. There are two tables UserId table & ID table. Check id from ID table with Date & where it falls in UserId table column- StartDate, EndDate depends on that assign userid for that Id. If the End date of UserId table blank consider Today().

UserId Table 

UserID	ID	StartDate	EndDate
A	1	04-09-2018	03-03-2019
B	1	03-03-2019

ID Table

ID	Date	Expected UserID
1	01-09-2018
1	02-09-2018
1	03-09-2018
1	04-09-2018
1	05-09-2018	A
1	17-10-2018	A
1	01-03-2019	A
1	02-03-2019	A
1	03-03-2019	A
1	04-03-2019	B
1	05-03-2019	B
1	06-03-2019	B
1	1-1-2020	B

[Greg_Deckler]

If you have duplicates, use MAXX with a FILTER instead of LOOKUPVALUE.

[az38 · Accepted solution]

hi @Anonymous 

try a measure

Expected UserID =
CALCULATE(FIRSTNONBLANK('UserId Table'[UserId], 1),
FILTER(ALL('UserId Table'), 'UserId Table'[StartDate] < SELECTEDVALUE('ID Table'[Date]) && ('UserId Table'[EndDate] >= SELECTEDVALUE('ID Table'[Date]) || ISBLANK('UserId Table'[EndDate]))
)