topic Re: LOOKUPVALUE error in DAX Commands and Tips

LOOKUPVALUE error

mhawkinsCU — Mon, 02 May 2022 19:10:30 GMT

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

Re: LOOKUPVALUE error

tamerj1 — Mon, 02 May 2022 20:21:11 GMT

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.

Re: LOOKUPVALUE error

mhawkinsCU — Mon, 02 May 2022 20:31:40 GMT

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?

Re: LOOKUPVALUE error

tamerj1 — Mon, 02 May 2022 20:42:39 GMT

Relationships for example

Re: LOOKUPVALUE error

mhawkinsCU — Mon, 02 May 2022 20:44:09 GMT

I have no relationships between these two tables.

Re: LOOKUPVALUE error

tamerj1 — Tue, 03 May 2022 07:10:37 GMT

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

Re: LOOKUPVALUE error

mhawkinsCU — Tue, 03 May 2022 16:02:28 GMT

Re: LOOKUPVALUE error

tamerj1 — Tue, 03 May 2022 16:50:24 GMT

Great

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

Re: LOOKUPVALUE error

mhawkinsCU — Tue, 03 May 2022 18:06:17 GMT

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.

Re: LOOKUPVALUE error

tamerj1 — Tue, 03 May 2022 18:37:50 GMT

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.

Re: LOOKUPVALUE error

mhawkinsCU — Tue, 03 May 2022 18:50:30 GMT

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.

Re: LOOKUPVALUE error

tamerj1 — Tue, 03 May 2022 18:57:43 GMT

The new column will be in which table?

Re: LOOKUPVALUE error

mhawkinsCU — Tue, 03 May 2022 18:59:12 GMT

It needs to be in the field_values table.

Re: LOOKUPVALUE error

mhawkinsCU — Tue, 03 May 2022 19:03:57 GMT

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

Re: LOOKUPVALUE error

mhawkinsCU — Tue, 03 May 2022 19:09:47 GMT

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.

Re: LOOKUPVALUE error

tamerj1 — Tue, 03 May 2022 19:11:33 GMT

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

Re: LOOKUPVALUE error

tamerj1 — Tue, 03 May 2022 19:15:13 GMT

then try

MAXX (

RELATEDTABLE ( field_value_choices ), field_value_choices[field_value_id] )

Re: LOOKUPVALUE error

mhawkinsCU — Tue, 03 May 2022 19:18:14 GMT

That worked when I changed it to

MAXX (

RELATEDTABLE ( field_value_choices ), field_value_choices[field_choice_id] )