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 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.

 

 

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.

     

8 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    5 years ago

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

    • th3h0bb5's avatar
      th3h0bb5
      Resolver II
      5 years ago

      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's avatar
        sevenhills
        Super User
        5 years ago

        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. )

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

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

    • th3h0bb5's avatar
      th3h0bb5
      Resolver II
      5 years ago

      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's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

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