Forum Discussion

th3h0bb5's avatar
th3h0bb5
Resolver II
5 years ago
Solved

Lookup Returning only Partial Match

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 retu...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

     

th3h0bb5's avatar
th3h0bb5
Resolver II
5 years ago

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's avatar
v-kkf-msft
Community Support
5 years ago

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.