LOOKUPVALUE matches only some of the records containing the same look-up value

[MonikaR]

Hi, would really appreciate some help with an unexpected LOOKUPVALUE issue!

I've got two tables, 'Users' that has [id] and [name] among other fields and it contains one entry per [id], and 'TimeEntries' that has [user_id] among other fields and it contains multiple time entries for the same [user_id]. I am trying to add a name column to the 'Time entries' table using the following forumula: 

name = LOOKUPVALUE(Users[name],Users[id],TimeEntries[user_id])

And the issue I'm seeing is that for a bunch of rows with a given [user_id] in the 'TimeEntries' table, some of them do get matched up with the correct name and for some of them the name column is left blank! E.g.

TimeEntries

user_id	name
1234	Name Surname
1234
1234	Name Surname
1234	Name Surname
1234

A couple of other things that I thought might be relevant:

• Data in both the 'Users' and the 'TimeEntries' tables are sourced from the same upstream system so the id's should all be cosistent at least in theory... I thought - clutching at straws - that maybe there were blank spaces surrounding some of the [user_id] values so have tried TRIM() on [user_id] but that didn't do anything.
• The id's are made up of digits but when the data is imported, they are 'text' type. I thought that maybe data type is creating an issue so have tried changing type to whole number in both tables but that didn't sort the problem out either.

Have you ever seen anything like this? Would really appreciate some help!

[v-alq-msft]

Hi, @MonikaR 

Based on your description, I have tried several times and the result of the 'name' column displayed normally no matter what data type the userid is. You may try to use the following formula.

name = LOOKUPVALUE(Users[name],Users[id],TimeEntries[user_id],max(Users[name]))

Please inform me of the result. I am glad to solve the problem for you. Thanks.

Best Regards

Allan

[MonikaR]

Hi @v-alq-msft , thanks for your suggestion. I've tried the formula but it didn't work unfortunately.

The good news is that I have resolved the issue by changing my data model a bit and creating a relationship between 'Users' and 'TimeEntries' tables. Now LOOKUPVALUE (as well as RELATE) works as expected. It's still not clear to me why it was not the case before as I didn't think you needed a relationship for LOOKUPVALUE to function but there you go...

[JarroVGIT]

This is indeed unexpected behaviour, and I was immediately thinking about data types. Changing it to whole numbers in both tables should have solved that, but please try to do so in Power Query and not in PBI Desktop mode. This also removes trailing spaces in the numbers. 

You might want to try this, it is actually the same (or should be the same, I think) but just to rule out everything:

name = 
VAR _curUserID = TimeEntries[user_id]
RETURN
LOOKUPVALUE(Users[name],Users[id],_curUserID)

This makes sure that when this is evaluated as a column, it first retrieves the current user_id before evaluating anything else. This might solve some weird relationship filtering that might be going on. Could you let me know if this helped at all? (small chance but I do want to make certain :))

Thanks!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[MonikaR]

Hi @JarroVGIT, thanks for your reply! I've tried changing the formula code as you've suggested with the first var to retrieve user_id, however it didn't work.

Will try the data type change in Query tomorrow as the data takes a while to refresh and I've hit the wall with this thing today so heading home for now. Will post an update tomorrow re. how that goes.