Lookup Returning only Partial Match

[th3h0bb5]

I have two tables: General Ledger (GL) and CustomerOrderHeader (COH). COH has a unique Primary Key. GL has the same value as a Foreign Key.

When I write a LOOKUP() Calculated Column, it only returns a value one time, even though there are multiple matches. The screenshot below captures why I'm confused. For Key 7677, I should see my LOOKUP() value every time. Why on earth does it only pull across a value for the first row? I've trimmed and cleaned both the PK and FK. They're both saved as Text as well.

[Ashish_Mathur]

Hi,

Your LOOKUPVALUE() formula should be a written as a calculated column formula in the General Ledger table - not as a measure.  

[th3h0bb5]

Hi Ashish,

If you reread my original post, you'll see I said "When I write a LOOKUP() Calculated Column...", so I'm already doing that.

[Ashish_Mathur]

Hi,

From the image that you have shared, it looks like a measure.  Share the link from where i can download your PBI file.

[th3h0bb5]

Hi Ashish,

Thanks for the feedback. Since the output of a measure can only be a scalar value within a filter context (that is, an aggregate), and the output of my DAX (i.e. CustomerOrderHeader[CUSTOMER NAME]) is a non-aggregated column reference, it would be impossible for this DAX to be a measure. As a measure, I'd use something like:

MeasureVersion=
CALCULATE(
     MIN(CustomerOrderHeader[CUSTOMER NAME]),
     USERELATIONSHIP(

          CustomerOrderHeader[CustomerOrderHeaderID],

          GeneralLedger[CustomerOrderHeaderID]

          )

     )

Unfortunately, I've tried that as well and it returns the same results (see my response to SevenHills below).

I'm afraid I can't send a link to the PBIX file. It's client data and about 1.2 GB in size. I've tried to recreate the failure on dummy data (which I would be able to send you a link to) but it works fine when I do it there, which just adds to my confusion. If my DAX code works fine on fake data, what on earth is wrong with my columns that prevents it from working on my actual data?

[v-kkf-msft · Accepted solution]

Hi @th3h0bb5 ,

I guess that there may be hidden characters (such as spaces) in the CustomerOrderHeaderID column in GL table, causing the values are not exactly match. It is recommended to check whether there are multiple "7677" in the drop-down list of the column. Or try the following formula to see if all values can be returned. 

Column = 
CALCULATE(
    MAX('CustomerOrderHeader'[CUSTOMER NAME]),
    FILTER(
        'CustomerOrderHeader',
        'CustomerOrderHeader'[CustomerOrderHeaderID] = EARLIER('General Ledger'[CustomerOrderHeaderID])
    )
)

If there are multiple "7677", or the formula only returns part of the value, then you can try the following ways.

1. Change the data types of these two columns to Whole number to eliminate hidden characters.

2. Or use fuzzy matching to perform the merge in Power Query Editor.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sevenhills]

Just curious ... what you are getting for RELATED(CustomerOrderHeader[CUSTOMER NAME]) ? (assuming u have relationship between GL and COH)

[th3h0bb5]

The entire model is quite complex with about 150 tables, so I haven't edited all the relationships necessary to test that hypothesis. However, I did use an inactive relationship and CALCULATE() with USERELATIONSHIP(). Frustratingly, it produces the same result as the screenshot above. The DAX code for that is below. I've also successfully used a SQL LEFT JOIN on the PK and FK and it works fine. It's like there's something specific to Power BI that causes the join to fail.

[sevenhills]

UseRelationship, as per my knowledge, uses the existing relationship from the model. Hope this helps!

I tried to replicate

Both my measures worked

Lookup = LOOKUPVALUE( CustomerOrderHeader[Customer Name], CustomerOrderHeader[CustomerOrderHeaderID], SELECTEDVALUE(GeneralLedger[CustomerOrderHeaderID]))

Lookup2 = CALCULATE( 
  MIN(CustomerOrderHeader[Customer Name])
  , TREATAS( CALCULATETABLE(VALUES(GeneralLedger[CustomerOrderHeaderID]), GeneralLedger[CustomerOrderHeaderID]), CustomerOrderHeader[CustomerOrderHeaderID])
)

I think in case of virtual relationships, we normally tend to use TREATAS. 

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

(I have to multiple edits to this reply to get you the DAX. )