Lookup Value error

[Jebilaya]

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

[SpartaBI]

@Jebilaya just saw now that I missed your previuos reply. Sorry 🙂
Happy to see it was solved!

[Jebilaya]

No worries - i'm new on my PowerBI journey so picking it up as I go.

I'm sure I will have other questions @SpartaBI  - this community is really helpful

[Jebilaya · Accepted solution]

Resolved now - realised the uniqueID wasn't so unique so lookupvalue didn't work.

[Jebilaya]

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]-1

Return

LOOKUPVALUE('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

[SpartaBI]

@Jebilaya hey, it's really hard to see the details in the screenshot. Can you supply a sample data with the and an example of the desired result.