topic Re: Unable to pass a variable using LOOKUPVALUE in DAX Commands and Tips

Unable to pass a variable using LOOKUPVALUE

Anonymous — Wed, 29 Jul 2020 21:54:20 GMT

I'm currently trying to pull a value from a different row in a table using the LOOKUPVALUE command but I'm encountering a problem.

Landmark = VAR trailerid = 'Table'[Trailer ID] RETURN LOOKUPVALUE('Table'[Landmark], 'Table'[Index], 'Table'[Index Prior],'Table'[Trailer ID], trailerid)

Above is the code I've been trying to use but it doesnt work in its current state and I get the error "A table of multiple values was supplied where a single value was expected."

However, when I format like so:

Landmark Prior = LOOKUPVALUE('Table'[Landmark], 'Table'[Index], 'Table'[Index Prior],'Table'[Trailer ID], "8543")

The above code works properly when formatted manually for the trailer id "8543".

Is there a particular reason why trying to pass a variable through will not work on this code? I can provide more context if necessary, I'm just hoping I did something stupid thats easy to fix at first glance from somebody smarter than me.

Thanks

Re: Unable to pass a variable using LOOKUPVALUE

amitchandak — Thu, 30 Jul 2020 01:48:59 GMT

Anonymous , Try

minx(filter('Table','Table'[Index] = earlier( 'Table'[Index Prior]) && 'Table'[Trailer ID] = "8543"),'Table'[Landmark])

minx(filter('Table','Table'[Index] = earlier( 'Table'[Index]) && 'Table'[Trailer ID] = "8543"),'Table'[Landmark])

Re: Unable to pass a variable using LOOKUPVALUE

AntrikshSharma — Thu, 30 Jul 2020 04:09:44 GMT

Are you trying to use LOOKUPVALUE on the dimension table and the values are on the many side of the relationship? If yes, then you should use an Iterator like FILTER & RELATEDTABLE to get a list of all the values that are related to the current row in the table.