Forum Discussion
Solved
Lookup Value error
I am trying to get a calculated column to return an alternative drop departure time.
I've created a column to give a unique route and drop ID for each row and I need to be able to return the planned departure time for the previous drop. My lookupvalue formula is giving me a "A table of multiple values was supplied where a single value was expected." error
Any help appreciated
Resolved now - realised the uniqueID wasn't so unique so lookupvalue didn't work.
5 Replies
Resolved now - realised the uniqueID wasn't so unique so lookupvalue didn't work.
Not sure how to supply sample data SpartaBI - can't see an attach option but i've put a table below
The DAX i'm trying to use is
PlanDepartPrior =var dropprior = 'Microlise Files'[DropID]-1ReturnLOOKUPVALUE('Microlise Files'[Planned departure],'Microlise Files'[UniqueDrop],CONCATENATE('Microlise Files'[UniqueRoute],dropprior))So basically drop 2 should return the departure time of drop 1
Order Reference Actual arrival Actual departure Planned arrival Planned departure UniqueDrop DropID Expected Result 1024657582 1899-12-30 06:47:00 1899-12-30 06:56:00 1899-12-30 06:43:00 1899-12-30 06:50:00 5/15/2022259-101 1 Blank 1025032718 1899-12-30 09:34:00 1899-12-30 09:46:00 1899-12-30 09:47:00 1899-12-30 09:58:00 5/15/2022259-1010 10 1899-12-30 09:43:00 1005202623 1899-12-30 09:50:00 1899-12-30 10:03:00 1899-12-30 10:02:00 1899-12-30 10:15:00 5/15/2022259-1011 11 1899-12-30 09:58:00 1024959827 1899-12-30 10:08:00 1899-12-30 10:15:00 1899-12-30 10:15:00 1899-12-30 10:31:00 5/15/2022259-1012 12 1899-12-30 10:15:00 1025080963 1899-12-30 10:22:00 1899-12-30 10:27:00 1899-12-30 10:37:00 1899-12-30 10:42:00 5/15/2022259-1013 13 1899-12-30 10:31:00 1024861453 1899-12-30 10:36:00 1899-12-30 10:42:00 1899-12-30 10:50:00 1899-12-30 10:58:00 5/15/2022259-1014 14 1899-12-30 10:42:00 1023915193 1899-12-30 10:52:00 1899-12-30 10:59:00 1899-12-30 11:03:00 1899-12-30 11:28:00 5/15/2022259-1015 15 1899-12-30 10:58:00 1024341888 1899-12-30 11:06:00 1899-12-30 11:10:00 1899-12-30 11:35:00 1899-12-30 11:42:00 5/15/2022259-1016 16 1899-12-30 11:28:00 1024904812 1899-12-30 11:18:00 1899-12-30 11:30:00 1899-12-30 11:49:00 1899-12-30 12:00:00 5/15/2022259-1017 17 1899-12-30 11:42:00 1024723657 1899-12-30 11:35:00 1899-12-30 11:43:00 1899-12-30 12:03:00 1899-12-30 12:12:00 5/15/2022259-1018 18 1899-12-30 12:00:00 1023868591 1899-12-30 11:59:00 1899-12-30 12:05:00 1899-12-30 12:15:00 1899-12-30 12:23:00 5/15/2022259-1019 19 1899-12-30 12:12:00 1024346227 1899-12-30 07:05:00 1899-12-30 07:17:00 1899-12-30 07:00:00 1899-12-30 07:19:00 5/15/2022259-102 2 1899-12-30 06:50:00 1025112226 1899-12-30 07:40:00 1899-12-30 07:49:00 1899-12-30 07:21:00 1899-12-30 07:56:00 5/15/2022259-103 3 1899-12-30 07:19:00 1024849229 1899-12-30 07:56:00 1899-12-30 08:01:00 1899-12-30 08:03:00 1899-12-30 08:10:00 5/15/2022259-104 4 1899-12-30 07:56:00 1024431577 1899-12-30 08:03:00 1899-12-30 08:07:00 1899-12-30 08:11:00 1899-12-30 08:17:00 5/15/2022259-105 5 1899-12-30 08:10:00 1025107193 1899-12-30 08:11:00 1899-12-30 08:13:00 1899-12-30 08:20:00 1899-12-30 08:27:00 5/15/2022259-106 6 1899-12-30 08:17:00 1025056921 1899-12-30 08:18:00 1899-12-30 08:21:00 1899-12-30 08:31:00 1899-12-30 08:36:00 5/15/2022259-107 7 1899-12-30 08:27:00 1024813974 1899-12-30 08:39:00 1899-12-30 08:55:00 1899-12-30 08:42:00 1899-12-30 08:52:00 5/15/2022259-108 8 1899-12-30 08:36:00 1025104007 1899-12-30 09:18:00 1899-12-30 09:28:00 1899-12-30 09:33:00 1899-12-30 09:43:00 5/15/2022259-109 9 1899-12-30 08:52:00
