Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

LOOKUPVALUE does not find data that does exist

Hello

Does anyone know why I only find a line when there are really more coincidences?

Measure:

Contact Name = LOOKUPVALUE(contactos_sql[Name],Contactos_sql[No_],Sales_Header_sql[Sell-to Contact No_])

Between these two tables there is a non-active relationship

  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi Syndicate_Admin 
    There are reasons for returning blanks when using lookupvalue function:

     

    • No Matching Value:

      • If there is no row in the table that matches the provided search conditions, LOOKUPVALUE will return a blank.

    • Multiple Matching Values:

      • If there are multiple rows that match the search conditions, LOOKUPVALUE will also return a blank to indicate ambiguity.

    • Missing or Blank Values in the Lookup Column:

      • If the column being looked up contains blanks or is missing for the matching rows, LOOKUPVALUE will return a blank.

    For more assistance :

    please share a pbix or some dummy data that keeps the raw data structure with expected results. It should help us clarify your scenario and test the coding formula.

    How to Get Your Question Answered Quickly  

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

     

6 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Syndicate_Admin - I hope there is an inactive relationship between the tables, create a below measure 

    Contact Name =
    CALCULATE(
    LOOKUPVALUE(
    Contactos_sql[Name],
    Contactos_sql[No_], Sales_Header_sql[Sell-to Contact No_]
    ),
    USERELATIONSHIP(Contactos_sql[No_], Sales_Header_sql[Sell-to Contact No_])
    )

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      2 years ago

      With the measure you comment I get the following error:

      It is true that in the contacts table there are no repetitions but in the Sales header table there are

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi Syndicate_Admin 
    There are reasons for returning blanks when using lookupvalue function:

     

    • No Matching Value:

      • If there is no row in the table that matches the provided search conditions, LOOKUPVALUE will return a blank.

    • Multiple Matching Values:

      • If there are multiple rows that match the search conditions, LOOKUPVALUE will also return a blank to indicate ambiguity.

    • Missing or Blank Values in the Lookup Column:

      • If the column being looked up contains blanks or is missing for the matching rows, LOOKUPVALUE will return a blank.

    For more assistance :

    please share a pbix or some dummy data that keeps the raw data structure with expected results. It should help us clarify your scenario and test the coding formula.

    How to Get Your Question Answered Quickly  

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

     

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      2 years ago

      The problem then is that there are multiple coinciding values. Is there a way for anyone I find to indicate it (even if there are several)?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi apenaranda 

         

        Can you show some sample data of two tables in the table format? You say there are multiple coinciding values, do you mean one No_ value may have multiple Name values in the Contactos_sql table? Additionally, what is the cross-filter direction of the inactive relationship?

         

        Best Regards,
        Jing