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

[Anonymous]

Hello,

I am having that error when I create this formula. May you help me ? 

Master Billing = LOOKUPVALUE('Billing (2)'[Billing],'Billing (2)'[Merged.1],Forecast[Merged.1])

but  it worked other formula whioch is below. so I am confused. 

Forecast-Fore = LOOKUPVALUE(Forecast[Forecast USD],Forecast[Merged],'Billing (2)'[Merged])

Thank you in advance

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on the description, the problem may be due to the LOOKUPVALUE function is returning more than one value for the given condition. There are duplicate values in the column being searched.

Check that for each value in 'Billing (2)'[Merged.1], there is only one corresponding value in Forecast[Merged.1].

Then, try using the aggregator function to ensure that only a single value is returned.

Master Billing = CALCULATE(MAX('Billing (2)'[Billing]), FILTER('Billing (2)', 'Billing (2)'[Merged.1] = Forecast[Merged.1]))

You can view the following document to learn more information.

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

MAX function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

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

[mohammadyousaf]

You should have unique values in 'Billing (2)'[Merged.1] if not unique and you expect multiple matching values then use Max, Min Or Sum with Dax. 

Try this.. 

Master Billing = CALCULATE(MAX('Billing (2)'[Billing]), FILTER('Billing (2)', 'Billing (2)'[Merged.1] = Forecast[Merged.1]))

[Anonymous]

Hello Mohammad,

Thank you for your reply but I am having different problem. there is no error but I can not see right totals on the table. if you want I can share data with you to your e-mail addresses.

Regards

Baris 

[mohammadyousaf]

Please message me your data let me have a look. 

[Kedar_Pande]

@Anonymous 

In the first formula, check if the columns 'Billing (2)'[Merged.1] and Forecast[Merged.1] are properly related and of the same data type. If the column names or types differ, the formula will fail.

Confirm there is a relationship between the tables, or that the lookup is valid across unrelated tables.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous]

Hi Kedar,

Thank you for your reply.  with your recommendation, all the types are same. 

Regards

Baris