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

Lookupvalue returning error

Hi,

 

I have a table which has movements for all assets. 

For this one particular asset - 59397, I am trying to get the date it was last used and the lookup errors when I use the formula: 

 

LUp = LOOKUPVALUE(AssetMovementLine[CreatedDateTime], AssetMovementLine[New], AssetMovementLine[New]+1)

New is conactenating the AssetId and Rank.

 

AssetMovementIDAssetIDCreatedDateTimeRankNew
507825939716/07/2018 8:499593979
47468593976/02/2018 10:418593978
472465939717/01/2018 14:347593977
388355939727/06/2016 12:176593976
38291593972/05/2016 12:075593975
312685939718/12/2014 14:054593974
164355939710/09/2012 9:323593973
164315939710/09/2012 9:232593972
156385939723/07/2012 8:111593971

 

  
Any help will be appreciated.

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
praiselyabraham Regular Visitor
Regular Visitor

Re: Lookupvalue returning error

In that case you can take 2 approaches:
1. make sure the [New] column you create is having unique values; something like [rank-asset_id] instead of just concatenating, 

2. Use the below DAX
LookUpDate = LOOKUPVALUE(AssetMovementLine[CreatedDateTime], AssetMovementLine[AssetID], AssetMovementLine[AssetID] , AssetMovementLine[Rank],AssetMovementLine[Rank]+1)

.

However I would suggest you to follow the 1st approach as the second one is bit performance costly .

Hope this helps your issue.

4 REPLIES 4
praiselyabraham Regular Visitor
Regular Visitor

Re: Lookupvalue returning error

Hi , 

 

I am not sure what exactly your issue is, I tried replicating it in my end and it worked as normal. Providing the screenshot of it.

Capture.PNG

Regards,

Praisely

Stharwani Regular Visitor
Regular Visitor

Re: Lookupvalue returning error

Capture1.PNG

Highlighted
Stharwani Regular Visitor
Regular Visitor

Re: Lookupvalue returning error

I am getting this possibly because there are more assets in the table?

praiselyabraham Regular Visitor
Regular Visitor

Re: Lookupvalue returning error

In that case you can take 2 approaches:
1. make sure the [New] column you create is having unique values; something like [rank-asset_id] instead of just concatenating, 

2. Use the below DAX
LookUpDate = LOOKUPVALUE(AssetMovementLine[CreatedDateTime], AssetMovementLine[AssetID], AssetMovementLine[AssetID] , AssetMovementLine[Rank],AssetMovementLine[Rank]+1)

.

However I would suggest you to follow the 1st approach as the second one is bit performance costly .

Hope this helps your issue.