I'm at a loss how to make this work. Essentailly i'm working with transactions of components and trying to work out days elapsed between the two transactions (Removal, repair order created).
The original data is a row by row of transaction type that also has a transaction date. Each component has a batch number which is included in the data so to simplify things I split the original table into two seperate tables and use the batch number as a relationship between them. Below is a summarised version of the data.
Removal Table
| Transaction | Batch | Removal Date |
| Removal | 1 | 1, January 2021 |
| Removal | 2 | 2, January 2021 |
| Removal | 3 | 4, January 2021 |
RO create Table
| Transaction | Batch | Order number | Created Date |
| RO Create | 1 | A | 3, January 2021 |
| RO Create | 2 | B | 5, January 2021 |
| RO Create | 3 | C | 9, January 2021 |
In order to work this out i thought i could do a LOOKUPVALUE to bring the Created Date into the Removals Table and then use DATEDIFF to work out the days elapsed.
I tried using this DAX formula for the new column RO Date = LOOKUPVALUE(RO[Created Date], RO[Batch], Removal[Batch])
But I get the error message "A table of multiple values was supplied where a single value was expected." I've tried switching the two batch columns around and get the same issue. I've looked for a solve and found a few similar issues but none of the solutions offered work for me.
Greatly appreciate any assistance here.
