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

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