LOOKUPVALUE-A table of multiple values was supplied where a single value was expected.

[Saxon10]

Hi,

I have a two table are Table1 and Table2.

I am trying to do lookupvalue DAX function "Lookupvalue =LOOKUPVALUE(TABLE2[TEX],TABLE2[ITEM],TABLE1[ITEM])" from Table2 to Table 1 but I am receiving the following error message "A table of multiple values was supplied where a single value was expected".

In Table2 the following items 284 and 685 has duplicate text and 686 has duplicated but unique text. In Table 1 I don't have any duplicate enrty. If we have a duplicate entry then pick the first text value from table2 to table1 according to the item.

Table1:

ITEM	TEX
123	RCB
124	CHR
125	RCB
126	RCB
127	CHR
128	CHR
129	RCB
130	CHR
131	CHR
284	RCB
132	RCB
133	RCB
134	RCB
135	RCB
136	RCB
137	RCB
138	RCB
139	RCB
140	RCB
141	RCB
142	RCB
143	RCB
144	RCB
145	RCB
146	RCB
147	RCB
148	RCB
149	RCB
685	RCB
686	RCB

Table2:

ITEM	TEX
123	RCB
124	CHR
125	RCB
126	RCB
127	CHR
128	CHR
129	RCB
130	CHR
131	CHR
284	DEL
284	RCB
132	RCB
133	RCB
134	RCB
135	RCB
136	RCB
137	RCB
138	RCB
139	RCB
140	RCB
141	RCB
142	RCB
143	RCB
144	RCB
145	RCB
146	RCB
147	RCB
148	RCB
149	RCB
685	DEL
685	RCB
685	RCB
686	RCB
686	RCB
686	RCB

[sanalytics · Accepted solution]

@Saxon10 ,
Lookupvalue sould return error..because  Table 2 have duplicate data.(Item 685,686).
if you want to pull out the value from table 2 to table 1 then your have to take first value.Try this measure,

Tex from Table 2 = CALCULATE(FIRSTNONBLANK('Table 2'[TEX],TRUE()), FILTER('Table 2','Table 2'[ITEM]=Table1[ITEM] ) )

You can find the pbix file from below link.

https://dropfiles.org/bMsjibe9

hope that will help

Regards,

sanalytics

if it is your solution please accept this as solution and like.

[Prima_]

Hi, 

The above CALCULATE returns one value for the matching item. What if I want to retrieve the TEXT (in my case, it's another column. I don't have that column in table 1) from Table 2 for all the matching items in Table 1. 

Thank you.

[Saxon10]

thanks for your reply. I will check quickly and update the feedback to you. Can you please explain what's the following function actually doing hete calculate and firstnonblank s9 it will help to understand the power bi DAX functionality 

[sanalytics]

@Saxon10

Sure.. I just used filter instead of Lookupvalue..
filter is an iterative function.it goes to the table 2 and picks up the item and compare to the table 1 item.if the two items are equal filter will take those items make a virtual table and pass to the calculate and calculate simply gives you the first value of table 2 which is item is equal to table 1 item . Since your table 2 date duplicate record so i had to use the firstnonblank function.

that's it.

Hope this will help

Regards,

sanalytics

if this is your solution please accept this as solution and like please

[spbiadmin]

Hello sanalytcis, thank you for providing this solution. Could you please let me know if multiple conditions could be evaluated using the Filter function?

[Saxon10]

thanks for your explaining and solution.