LOOKUPVALUE: Expressions that yield variant data-type cannot be used to define calculated columns

[rambler]

Hi All

This error message has been addressed in multiple posts, but I cannot seem to find a solution for my LOOKUPVALUE problem.

I have two imported tables that I want to use a LOOKUP value DAX formula in.

Table1

Column1 Int64

Table2

Column1 Int64

Column2 text

(taken from the metadata in PowerQuery)

Formula in table1 for a calculated column:

ColName = LOOKUPVALUE(Table2[Column2], Table2[Column1], Table1[Column1], 0)

This brings back the error message, "Expressions that yield variant data-type cannot be used to define calculated columns"

Any idea on how to cast or covert values?

[Zubair_Muhammad · Accepted solution]

@rambler 

try this

ColName =
LOOKUPVALUE ( Table2[Column2], Table2[Column1], Table1[Column1], 0 & "" )

[rambler]

@Zubair_Muhammad 

Thank you so much! That gives the exact result that I am after!  🙂

Thank you also for the swiftness of your response.

Just out of interest, how does that addition work?

kind regards

Rambler

[Zubair_Muhammad]

@rambler 

Hi,

It just converts the datatype of alternate result from a number 0 to text 0 which is consistent with the datatype of Table2[Column2] which you are looking for.

All values in the column must have same data type

[rambler]

@Zubair_Muhammad  - thanks for the explanation!

Rambler