Forum Discussion

JohnF5343's avatar
JohnF5343
Regular Visitor
4 years ago
Solved

Function 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer.

Hi, 

I'm creating a new column in a table with following DAX. All the columns in the DAX are text. The number column has the following format example: TWD123, SalesOrgH has the following format example: RTW001|WDC001|ERD001|EDG001.

 

Org 1 Email = 
LOOKUPVALUE (
    'Test'[email],
    'Test'[number], PATHITEM ( Test[SalesOrgH], 1, 1 )
)

I'm getting the following error: Function 'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

 

I've tried changing the number column to a number but I get an error. 

 

How can I solve this one? 

 

Thanks for the help. 

Need Help

8 Replies

    • JohnF5343's avatar
      JohnF5343
      Regular Visitor
      4 years ago

      Hi, 

       

      So I have 2 tables in the pbix, the SalesPerson table works fine with whole numbers in the employee id column. With the other table, I get an error message. Could you have a look please? Pbix file 

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Test[number] is a Text type, so the PATHITEM has to return text.  The 3rd parameter defines the type returned so please look at that.

     

    • JohnF5343's avatar
      JohnF5343
      Regular Visitor
      4 years ago

      Hi, they are all text fields. What should I do? Thanks. 

    • JohnF5343's avatar
      JohnF5343
      Regular Visitor
      4 years ago

      Not very far. 🙂

      I tried the following but still get an error. 

      Org 1 Email = 
LOOKUPVALUE (
    'Table'[EmployeeEmail],
    'Table'[EmployeeID], FORMAT( PATHITEM ( 'Table'[OrgH], 1, 1 ),"")
)

       

      • HotChilli's avatar
        HotChilli
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Why are you trying to use FORMAT?  The 3rd parameter of PATHITEM defines the return type.

        "If missing or TEXT or 0 then this function returns a string. If INTEGER or 1 then this function returns an integer."

        The LOOKUPVALUE that doesn't work is using PATHITEM with a 1 in the 3rd parameter.  Try the other option.