Assistance Needed with Lookup Function Issue in Power BI

[ZakD]

Dear Power BI Community,

I hope this message finds you well.

I am currently facing a challenge with a lookup function in my table view within Power BI. Although the function operates correctly for most records, it inexplicably fails for certain entries, including instances involving the same customer. I have attached a screenshot to provide a clearer illustration of the issue.

Could anyone provide insights or share if they have experienced similar issues? I appreciate any guidance you can offer.

Thank you for your support.

Best regards,

Zak.

[ZakD · Accepted solution]

Among the solutions considered, I have selected the DAX function (MINX and FILTER). As a reminder, this is the  formula syntax:

---------------------------------------------------------------------------------------------------------------
ResultColumn = MINX(
FILTER(
Table2,
Table2[ID] = Table1[ID]
),
Table2[ValueToLookup]
)

Thanks again @HotChilli for your support, I could sort this thanks to your guidance.

[ZakD]

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 

[HotChilli]

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.

[ZakD]

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

[ZakD]

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

What do you mean by hardcoding the lookupvalue?

[HotChilli]

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)

[ZakD]

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 🙂

[HotChilli]

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.