topic Re: How to resolve the Error: A table of multiple values was supplied where a single value was expected in DAX Commands and Tips

How to resolve the Error: A table of multiple values was supplied where a single value was expected

saqib1 — Mon, 22 Jan 2024 10:33:03 GMT

Hi,

I have seen other posts regarding this error also, but none of them could resolve the issue in my case, so here goes:

A certain product is produced through 5 processes. Depending upon the quality of finished goods required, the raw material can be used in low, normal, or high quantities. Based on a slicer for selecting the RM input level and a separate slicer for units produced of the product, the total raw material consumed to produce X products with low/normal/high raw material to be selected through slicer is required.

Input Level	Process	RM in Kgs
Low	1	10
Low	2	12
Low	3	14
Low	4	16
Low	5	18
Normal	1	12
Normal	2	14
Normal	3	16
Normal	4	18
Normal	5	20
High	1	14
High	2	16
High	3	18
High	4	20
High	5	22

Example: If "Low" RM Input level is selected, then each row of the low category should get multiplied with the selected units produced and divided by 50000 to get converted into bags.

If 5,000 units are selected and input level is low, answer should be = 7 bags

As seen in the screenshot, the DAX:

Total RM Consumed =

var a = CALCULATE(VALUES('RM Input Level'[RM in Kgs]),

FILTER('RM Input Level','RM Input Level'[Input Level]= SELECTEDVALUE('RM Input Level'[Input Level])))

RETURN

SUMX('RM Input Level', a* 'Units Produced'[Units Produced Value])/ 50000

(Need to divide by 50000 to convert the RM into bags)

is giving error : A table of multiple values was supplied where a single value was expected.

kindly guide how to resolve this issue.

https://1drv.ms/u/s!AoqwAyWfotQsjmRi1tAHunMH0nH3?e=zR3AVp

Re: How to resolve the Error: A table of multiple values was supplied where a single value was expected

some_bih — Mon, 22 Jan 2024 11:06:07 GMT

Hi saqib1

try version v2 below

Total RM Consumed_v2 =

var new_x=SELECTEDVALUE('RM Input Level'[Input Level])))

var a = CALCULATE(VALUES('RM Input Level'[RM in Kgs]),
FILTER('RM Input Level','RM Input Level'[Input Level]= new_x

RETURN
SUMX('RM Input Level', a* 'Units Produced'[Units Produced Value])/ 50000

Re: How to resolve the Error: A table of multiple values was supplied where a single value was expected

ThxAlot — Mon, 22 Jan 2024 11:17:11 GMT

Re: How to resolve the Error: A table of multiple values was supplied where a single value was expected

saqib1 — Mon, 22 Jan 2024 11:20:26 GMT

Its still giving the same erorr.

Re: How to resolve the Error: A table of multiple values was supplied where a single value was expected

saqib1 — Mon, 22 Jan 2024 14:30:36 GMT

Thanks Alot ThxAlot 🙂 the DAX is working exactly as intended but can you kindly guide how does the formula select the input level even though there's no mention of it in the DAX?

Re: How to resolve the Error: A table of multiple values was supplied where a single value was expected

saqib1 — Tue, 23 Jan 2024 05:20:35 GMT

ThxAlot In the same data above, the units produced selection needs to be aggregated everytime in each subsequent process and then get multiplied by the RM. For example, if "Input level" is "low" and "Units produced" selected are 5000, then:

10*5000 + 12*10000 + 14*15000 + 16*20000 + 18*20000.

Can you kindly guide how can the DAX be modified for this?

Re: How to resolve the Error: A table of multiple values was supplied where a single value was expected

ThxAlot — Tue, 23 Jan 2024 11:12:19 GMT

Aha, there's a mysterious power called "filtering" in the world of DAX; it's everywhere in any possible forms like slicer, column/row in table/matrix, legend of other visuals, etc.

Seriously, "filtering" is the essence of DAX. You don't need to explicitly refer to certain table/column in a measure as they already function through certain slicer/relationship. The more practise, the more elegant you'll find it.

Re: How to resolve the Error: A table of multiple values was supplied where a single value was expected

ThxAlot — Tue, 23 Jan 2024 11:13:10 GMT

Glad to offer further help; but what's the logic for 10000, 15000, etc. ?

Re: How to resolve the Error: A table of multiple values was supplied where a single value was expected

saqib1 — Tue, 23 Jan 2024 12:31:03 GMT

Actually this is somewhat different scenario...the data is same as in the example given but number of units produced are taken to be aggregating with each process. i.e. In process 1, (if 5000 units selected) then 5000, in process 2, 10000, in process 3, 15000 and so on..

Re: How to resolve the Error: A table of multiple values was supplied where a single value was expected

saqib1 — Tue, 23 Jan 2024 12:34:23 GMT

Lol 😁 Thanks for guiding as well as for the humour.