cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Need help in Lookup with if condition

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 

UserIDIDStartDateEndDate
A131-03-200524-05-2005
B231-03-200519-04-2005
C309-08-200519-09-2005
E127-01-200621-04-2006
F221-02-200601-06-2006
G321-02-2006 
D413-09-2005 

 

ID Table;

IDExpected UserID
1E
2F
3G
4D

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Need help in Lookup with if condition

Hi @amolNv2 

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

1 REPLY 1
Highlighted
Super User II
Super User II

Re: Need help in Lookup with if condition

Hi @amolNv2 

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.