Error: LOOK UP VALUE does not support comparing values

[Anonymous]

Hi all,

I found the above error when trying to create a calculated column using the LOOKUPVALUE function.

So I have the following tables;

Table A
ID	AMOUNT	Decription	Country
567933-YYDD	1000	Red	USA
674456-UUII	200	Blue	UK
778456-FFTY	300	Yellow	Germany
567875-HHYU	20	Black	China
452217-PPYU	10	Green	Fiji

Table B
ID	AMOUNT	Decription	Country
23345-TTYU	3000	Red	USA
11233-YYTY	100	Blue	UK
00156-HYTR	50	White	Germany
778456-FFTY	30	Grey	Germany

Table C
ID	AMOUNT DIFF	Changes
674456-UUII		No Change
778456-FFTY	270	Yellow Grey
23345-TTYU		No Change
11233-YYTY		No Change

Table C is a summarized table using columns from Table A & B and the Changes column is a calculated column. Ideally, Table C monitors changes between Table A and B. 

The dax code used to calculate the column "AMOUNT DIFF" is;

AMOUNT DIFF =

var tab1 = LOOKUPVALUE(Table A[ID],Table A[Amount], Table C[ID])
var tab2 = LOOKUPVALUE(Table B[ID],Table B[Amount], Table C[ID])

Return tab2 - tab1

I get the  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.

The error is originating from ID being text and Amount being an Integer. I don't want to change the data type for the variable as it may affect other calculations I have in my data model. How then can I convert either variables (ID and Amount) to text or integer or viceversa?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please try the below calculated column.

I think AMOUNT has to be the first argument in the LOOKUPVALUE function.

AMOUNT DIFF CC =
VAR tab1 =
LOOKUPVALUE ( 'Table A'[AMOUNT], 'Table A'[ID], 'Table C'[ID] )
VAR tab2 =
LOOKUPVALUE ( 'Table B'[AMOUNT], 'Table b'[ID], 'Table C'[ID] )
RETURN
tab2 - tab1

[Anonymous]

Worked successfully, Thanks @Jihwan_kin

[wdx223_Daniel]

think it's better to create relationships between these tables by ID

then put the ID columns of Table C on the row, and create a measure

Amount Diff=sum(tableA[Amount)]-sum(TableB[Amount])