cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Community Blog

Community Blog

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

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors