Assistance Needed with Lookup Function Issue in Power BI

Please show the ID and client_id fields of records which you expect to work but are not working (and the datatype of these fields)

--

In general, LOOKUPVALUE returns blank if there isn't a match so prove to me that you have a match.

Repair Tab:

So this is the record as you can see it's the same client_ID, one record was correctly found and the two others were not

Customer Tab:

This is the customer id from the customer tab

There is nothing to prove, I mean, as you can see, it worked perfectly fine for one of the 3 records but not for the others.
I do have other examples as well in case you still need any proof 🙂

Thanks, it's helpful. You will understand why I ask for proof.

What's the datatype on the client_id? Has the type been changed at any point?

--

Test this by hardcoding the LOOKUPVALUE to look for client_id 1564.  If it returns values correctly then the client_id is not really 1564 for the rows that are not working (it might look like it but something will be amiss)

Data type is number, for both columns in their recepective tabs.

What do you mean by hardcoding the lookupvalue?

I think I got what you meant so created a test column where I looked for the client_id 1564 and I had the same result, I filtered the data in my repair tab to show only client_id 1564

Ah, good, now we are into weird territory.

Do you have a relationship between the tables? Can you test by making the relationship inactive?

As a test, can you try and merge inner join on 1564 in Power Query to see if it works?

Can you write alternative dax to test without using lookupvalue (i.e. MIN with a FILTER)?

These are the sort of things I would look at.

---

I've read posts over the years of LOOKUPVALUE behaving like this and people have suggested a data refresh problem or a relationship interfering. I suppose it's possible.  Maybe we can solve it here for everyone.

The relations had already been disabled due to numerous circular relationships. Consequently, I utilized the lookup function to manage this complexity.

As you suggested, initially I attempted to merge the tables using an inner join, but encountered connectivity issues with my ODBC driver. Subsequently, I implemented an alternative approach using the MIN and FILTER functions, which proved to be highly effective. I plan to apply the same method to extract the remaining fields required.

Based on this experience, it seems that using MIN and FILTER functions might be a more suitable solution than relying on a lookup value for our current needs.

Thanks for your help HotChilli