Lookup does not return value for all exact similar rows

[amirghaderi]

I have a strange issue with lookup function. In the below table, booking code description is a lookup from another table based booking code column. But, as you see, it does not return the value for EXACT the same record.s I have trimmed booking code column from both tables in query editor.

Booking Code Description = LOOKUPVALUE(t_BB[Description],t_BB[EA / Jobcard],t_A[Booking Code])

[Anupa_Jayakody]

have a scenario where data is there in the look up table, but generates blank values for some values only. What is the solution here??

[d_gosbell]

---

@Anupa_Jayakody wrote:

have a scenario where data is there in the look up table, but generates blank values for some values only. What is the solution here??

---

This usually means the some of the values in your two tables are not identical. It could be that one of them has some extended characters in them. There are things like non-breaking spaces and zero width spaces which can creap in via user input fields and the values look the same to the eye, but when you compare them on a character by character basis they are not identical. Often once you have found one example you can add some cleaning logic which will fix multiple occurances of this issue.

[Anonymous · Accepted solution]

Hi @amirghaderi ,

Try this. Please create a new calculated column.

Booking Code Description =
MAXX (
    FILTER ( 't_BB', 't_A'[Booking Code] = 't_BB'[EA / Jobcard] ),
    't_BB'[Description]
)

I look forward to hearing from you.

Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[JackBPO]

I had this same issue. The fix to keep the same Lookupvalue formula is to look at the data model relationships between the 2 tables. Sometimes, if there are ambiguous relationships or multiple relationships, it can cause unexpected behavior in DAX calculations.

After deleting one of the relationships the lookupformula worked pulling all the values correctly.

[amirghaderi]

Thanks for this. Yes it worked!.

But, would you please advise, if you know the reason the lookup function was not working?

[amirghaderi]

I have done all these checks and no difference. I did another test as well by making relationship betwen tables as well (I cant do that in the main model since it created loop in the table relationship). It is strange that the relationship works exactly as expected. but, look up does not.

[Anonymous]

Hi @amirghaderi ，

If this doesn't work for you, please consider sharing more details about it. And it would be great if there is a sample file without any sensitive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar.
2. Expected output from sample data.
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

Hi @amirghaderi ，

According to your dax expression，please check if the value in “t_A[Booking Code]” exactly matches the value in “t_BB[EA / Jobcard]”，are there any spaces or something in there that you didn't notice?

For example, I created two tables.

1.Try merging the “t_A[Booking Code]” with the “t_BB[EA / Jobcard]” in the Power Query Editor and check if there is a null value.

2.Try Trim and Clean on “t_A[Booking Code]” and “t_BB[EA / Jobcard]” in the Power Query Editor and check the result in desktop.

Hope this helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[amirghaderi]

They are all 13 character. But, I noticed a strange case.

The missing lookup is only for certain employees. Although there is no direct relationship between employee name and the lookup column

[d_gosbell]

can you try temporarily creating a new column which checks the length of the 2 booking code columns? I have seen instances in the past where some non-visible special characters like non-breaking spaces can sneak into the data which will break things like LOOKUPVALUE since the values are actually different even though this difference is not visible. If the length is different for rows 1 and 4 in your screenshot it means that you probably have some of these characters in your data.