Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Stharwani
Advocate I
Advocate I

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

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.

View solution in original post

4 REPLIES 4
praiselyabraham
Resolver II
Resolver II

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

Capture1.PNG

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.