Hello,
I need help to understand a certain behaviour I remarked with LOOKUPVALUE.
To make it simple, let's say I have 3 tables:
a) Fact table : containing the list of requests of a ticketing tool, with a lot of information and starting with the REQUEST_ID, unique, for each request
b) 2 side tables, presenting the same kind of info but built differently, for each Request (so, both also the field REQUEST_ID) with multiple lines per request (in fact, 1 line per day between the creation date and the closure date of each request).
--> Both side tables have a relationship with my fact table between the fields REQUEST_ID. There is no direct relationship between the 2 side tables
For the sake of the test and the issue, I just did a very simple (and stupid) formula in order to, in my side table A, retreive for each line the corresponding REQUEST_ID from my side table B :
LOOKUPVALUE('REQUEST-TREND'[REQUEST_ID],'REQUEST-TREND'[REQUEST_ID],REQUEST_TREND[REQUEST_ID])
My expected result, when reading the doc about LOOKUPVALUE, is that for each REQUEST_ID of side table A found in side table B, the formula will return my REQUEST_ID of side table B (even if there are multiple lines matching the criteria, since the return value is always the same).
The issue is that it works for all the lines, except one where the formula returns a blank! Which of course I don't understand.
What I don't understand even more, is that by deleting just the relation between my side table B and my fact table, then it works for all the lines!
To be complete in the case, the side table B is built via Power Query, based on the fact table, by "expandind" that one between the Creation date and Closure date for each REQUEST_ID of the fact table. The side table 1 is part of my data set imported as the other tables (so, built upfront in a Datamart).
Can someone please explain me that behaviour and why the LOOKUPVALUE seems to take into account the relationship between the tables?
Thanks in advance,
Elorian.
