No duplicates, but lookupvalue error says multiples values present

[Anonymous]

Hi,

I have a report, where I get the error message : "A table of multiple values was supplied where a single value was expected."

I have two tables: 1. all the info with a [key_ID] and 2. summary with [key_ID]

It should be really simple, I want to reference one column from tableA in tableB.

I used      name = LOOKUPVALUE (tableA [name], tableA[key_ID], tableB[key_ID])

1)In the power query I made sure there are no duplicates. [delete duplicates]

2)The [key_ID] in both tables have the same distinct value as rows, meaing no duplicates.

3) I did a duplicate check using:

CALCULATE(COUNT[key_ID]) , ALLEXECPT(tableA, [key_ID])

CALCULATE(COUNT[key_ID]) , ALLEXECPT(tableB, [key_ID])

Is there another reason, why this error is displayed except of duplicates?

Thanks

[Anonymous · Accepted solution]

@Ashish_Mathur 

thanks anyways.

I still don't know why LOOKUPVALUE doesn't work, but I worked around it with following DAX:

CALCULATE (SUM [name]), FILTER(tableA, [key_ID] = EARLIER([key_ID]))

I think it gives me the same result

[v-robertq-msft]

Hi, @Anonymous 

If you have solved your problem through your own method, would you like to mark your own reply as a solution so that others can learn from it too?

Thanks in advance!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Sorry but cannot figure out the problem.

[Ashish_Mathur]

Hi,

There is no mistake in your formula.  Share the link from where i can download your PBI file.

[Anonymous]

@Ashish_Mathur 

Here is the link

https://1drv.ms/u/s!AsNOXcTG8Uttgdk6snU67hV-kK0kTw?e=bR7Zsz

[Ashish_Mathur]

Hi,

This is happening because there are duplicates in the Name column of sheet1.  I can definitely see many cells being blank in that column.  So they definitely need to be filled up.  Even after that ensure that there are only unique entries in the Name column.

[Anonymous]

@Ashish_Mathur 

thanks for checking,

I tried to put the value "0" where there is a Blank space, and changed it from "text" to "integer", but it still gives the same error.

the blank space shouldn't be the issue, because the unique value is the [key_ID].

If I do a new column, with only "1" as its value (no unique value), there is no error!

The name column can't be unique values, because the number is a result of how many visitors came from wich area. If there is no visitor from one  area, then the  value would be 0, if there are several visitors then maybe 10 or 20, but no unique values.

I just realized that only one [place_id] "3272" is the issue. All the other data works fine. 

Would you mind checking again? 

I divided all the data. Data with no error in Sheet1 and the error data in sheet: 3272

https://1drv.ms/u/s!AsNOXcTG8Uttgdk6snU67hV-kK0kTw?e=SbGa1m