topic Receiving Error with DAX formula in DAX Commands and Tips

Receiving Error with DAX formula

HEHuggins — Mon, 10 Oct 2022 18:44:02 GMT

Hello Folks, I am brand new to Power bi and am trying to get through learning it. I wrote the following DAX formula - "Customer Order Amount = LOOKUPVALUE('Stock Inventory'[Unit Price],'Stock Inventory'[SKU ID],'Weekly Demand Sheet'[SKU ID])*'Weekly Demand Sheet'[Weeks Demand]) - I receive the following error - "A table of multiple values was supplied where a single value was expected" -

I am so frustrated (NOOBITIS) - I can't figure out how to fix this, so I can create my forecasting - can anyone help? I will be most grateful

Helen

Re: Receiving Error with DAX formula

v-yueyunzh-msft — Tue, 11 Oct 2022 01:31:34 GMT

Hi , HEHuggins

According to your description, when you use the LOOKUPVALUE() function, an error is reported. Right?

For this function, if we use the associated field to match multiple values, it will report an error, and I reproduced this error through test data.

My test data is this:

Since we want to get the [Unit Price] field in the Stock Inventory table, but we have two values [SKU ID]=4 in the table, the engine cannot tell which value needs to be obtained, resulting in an error:

For the case of duplicate values, we need to choose the maximum or minimum value according to the actual needs, and we can use the following measures to solve this problem:

Column = var _cuurent_id = 'Weekly Demand Sheet'[SKU ID] var _t= FILTER('Stock Inventory','Stock Inventory'[SKU ID]= _cuurent_id) var _unit_price = MAXX(_t ,[Unit Price]) return _unit_price

The result is as follows:

Best Regards,

Aniya Zhang

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

Re: Receiving Error with DAX formula

HEHuggins — Tue, 11 Oct 2022 14:12:16 GMT

I was wanting it to caluculate the demand - so weeks demand x unit price =

but I used this and then just added another column multiplying the two - so thank you 😄