Concatenate lookupvalue type errror

[Anonymous]

I am trying to concatenate 2 lookupvalues in order to get a unique key for every single planner per day. I am doing that by concatenating the DayNumber + UserNumber. This is what the calculated column looks like now. 

plannerID = 
VALUE(
   CONCATENATE(
      LOOKUPVALUE(
         table1[DayNumber], 
         table1[DayName], 
         DAY(table2[Date])
      ),
      LOOKUPVALUE(
         table3[userNumber],
         table3[user]
         table2[creationUser]
      )
   )
)

table1[DayNumber] = int 

table1[DayName] = string

table2[Date] = Date but it takes the DAY()

table3[UserNumber] = int
table3[user] = sting

table2[creationUser] = int

The only problem is now it gives me the error: "in the function LOOKUPVALUE is being compared to type text with values of type integer. Consider the use of VALUE or FORMAT to convert the values." But i'm already using VALUE. I'd appreciate some help. Thanks in advance

[Fowmy · Accepted solution]

@Anonymous 

You need to convert the search value to text as the search column is text in both lookups:

plannerID = 

   CONCATENATE(
      LOOKUPVALUE(
         table1[DayNumber], 
         table1[DayName], 
         FORMAT(DAY(table2[Date]),"@")
      ),
      LOOKUPVALUE(
         table3[userNumber],
         table3[user],
         FORMAT(table2[creationUser],"@")
      )
   )

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn