LOOKUPVALUE error

[mhawkinsCU]

I am getting the "A table of mulitpule values was supplied were a single value was expceted."

I am trying to look up the field_value_choices[field_choice_id] based on the field_values[id]

This formula is being used in a calculated column. 

I am stuck as to way I am getting this error.

Here is my formula 

Employee Type= LOOKUPVALUE(field_value_choices[field_choice_id],field_value_choices[field_value_id],field_values[id])

field_values

field_value_choices

[mhawkinsCU]

So the lookup value have to be unique? I am trying to determine the field_choice_id by checking the field_vaule_id. 

What details about my data model would be helpful?

[tamerj1]

Relationships for example 

[tamerj1]

Hi @mhawkinsCU 

it seems that the field_value_choices[field_choice_id] column does no contain unique values. Please provide more details about your data model. 

[mhawkinsCU]

I have no relationships between these two tables. 

[tamerj1]

@mhawkinsCU 

Sorry for the late reply. 
If possible please create a relationship between 

field_value_choices[field_choice_id]

and

field_values[id]

and let me know the cardinality and the crossfilter type you get. 
based on that we can retrieve values using RELATED or RELATED table. 
In case you have multiple 

field_value_choices[field_value_id]

for each field_values[id] then you have to advise which one of them should be retrieved 

[mhawkinsCU]

[tamerj1]

Great

would you please answer the question at end of my previous reply 

[mhawkinsCU]

If you are asking about the Cardinality and crossfilter type that is why included the Edit relationship details in the picture.

It is many (field_value_choices) to 1 (field_values), Cross filter direction is single.  

[tamerj1]

Actually no. What I mean is that there is no single value for the ID. Retrieving the ID in the the field values table will result in a table rather than a single value. Therfore, you have to provide a preference on which on of the ID's shall be selected. Unless you are sure that this table actually contains duplicates of the same ID. 

[mhawkinsCU]

I am sorry but I am not following what you are asking for.

I am trying to use the field_values[id] in the field_values table and look up that on the field_value_choices table were 

field_value_choices[field_value_id] 

and return the 

field_value_choices[field_choice_id]

I am using a calcualted column so there should only be 1 field_values[id] at a time. 

[tamerj1]

The new column will be in which table?

[mhawkinsCU]

It needs to be in the field_values table. 

[mhawkinsCU]

so basically what I am trying to have show in the Employee type column is - this is the field_values table

take the id 89088567

look on the field_value_choices table - match the id to teh field_value_id and return teh field_choice_id - 160597

[tamerj1]

But the field value choices table is the many side which means that for some ID's (at least one) you have multiple matches. Otherwise, the relationship would be one to one not one to many. And this is the source of the error in your formula. It could be blanks that is causing the issue. You need to double check your source data

[mhawkinsCU]

It does appear there are duplicate values in the field_value_id column as there are 7,444 rows but only 6,315 distinct values.

[tamerj1 · Accepted solution]

Ok

then try

MAXX (

RELATEDTABLE ( field_value_choices ), field_value_choices[field_value_id] )

[mhawkinsCU]

That worked when I changed it to 

MAXX (

RELATEDTABLE ( field_value_choices ), field_value_choices[field_choice_id] )