Solved: A table of multiple values was supplied where a si...

Anonymous · ‎03-21-2017

Hi!

I hope somebody can help me with this.

I'm stuck with an error: A table of multiple values was supplied where a single value was expected.

I get the error when I try to visualize next mesaure in any visual object:

Inflación = 
IF(
	LEFT(
		VALUES(
			'Zona-Gestión'[IdZonaGestion]
		);
		3
	) = "CEA";
	'Inflación País'[Inflación CEAM];
	IF(
		LEFT(
			VALUES(
				'Zona-Gestión'[IdZonaGestion]
			);
			3
		) = "DOM";
		CALCULATE(
			SUM(
				'Inflación País'[Inflacion]
			);
			FILTER(
				'País';
				'País'[IdPais] = "DOM"
			);
			LASTDATE(
				'Inflación País'[Fecha]
			);
			USERELATIONSHIP(
				'Inflación País'[IdPais];
				'País'[IdPais]
			)
		);
		CALCULATE(
			SUM(
				'Inflación País'[Inflacion]
			);
			LASTDATE(
				'Inflación País'[Fecha]
			);
			USERELATIONSHIP(
				'Inflación País'[IdPais];
				'País'[IdPais]
			)
		)
	)
)

This is the table 'Inflacion Pais':

IdPeriodo	IdPais	Inflacion	Fecha
012016	ARG	0,3	1/1/2016 0:00:00
022016	ARG	0,343	1/2/2016 0:00:00
032016	ARG	0,357	1/3/2016 0:00:00
042016	ARG	0,419	1/4/2016 0:00:00
052016	ARG	0,44	1/5/2016 0:00:00
062016	ARG	0,459	1/6/2016 0:00:00
072016	ARG	0,466	1/7/2016 0:00:00
082016	ARG	0,442	1/8/2016 0:00:00
092016	ARG	0,426	1/9/2016 0:00:00
102016	ARG	0,446	1/10/2016 0:00:00
012016	BOL	0,024	1/1/2016 0:00:00
022016	BOL	0,026	1/2/2016 0:00:00
032016	BOL	0,033	1/3/2016 0:00:00
042016	BOL	0,041	1/4/2016 0:00:00
052016	BOL	0,05	1/5/2016 0:00:00
062016	BOL	0,041	1/6/2016 0:00:00
072016	BOL	0,035	1/7/2016 0:00:00
082016	BOL	0,035	1/8/2016 0:00:00
092016	BOL	0,035	1/9/2016 0:00:00
102016	BOL	0,035	1/10/2016 0:00:00
012016	BRA	0,107	1/1/2016 0:00:00
022016	BRA	0,104	1/2/2016 0:00:00
032016	BRA	0,094	1/3/2016 0:00:00
042016	BRA	0,093	1/4/2016 0:00:00
052016	BRA	0,093	1/5/2016 0:00:00
062016	BRA	0,088	1/6/2016 0:00:00
072016	BRA	0,087	1/7/2016 0:00:00
082016	BRA	0,09	1/8/2016 0:00:00
092016	BRA	0,085	1/9/2016 0:00:00
102016	BRA	0,078	1/10/2016 0:00:00
012016	CHI	0,049	1/1/2016 0:00:00
022016	CHI	0,048	1/2/2016 0:00:00
032016	CHI	0,046	1/3/2016 0:00:00
042016	CHI	0,043	1/4/2016 0:00:00
052016	CHI	0,043	1/5/2016 0:00:00
062016	CHI	0,042	1/6/2016 0:00:00
072016	CHI	0,04	1/7/2016 0:00:00
082016	CHI	0,033	1/8/2016 0:00:00
092016	CHI	0,03	1/9/2016 0:00:00
102016	CHI	0,028	1/10/2016 0:00:00
012016	COL	0,075	1/1/2016 0:00:00
022016	COL	0,076	1/2/2016 0:00:00
032016	COL	0,08	1/3/2016 0:00:00
042016	COL	0,08	1/4/2016 0:00:00
052016	COL	0,082	1/5/2016 0:00:00
062016	COL	0,086	1/6/2016 0:00:00
072016	COL	0,09	1/7/2016 0:00:00
082016	COL	0,081	1/8/2016 0:00:00
092016	COL	0,073	1/9/2016 0:00:00
102016	COL	0,065	1/10/2016 0:00:00
012016	CRC	-0,002	1/1/2016 0:00:00
022016	CRC	0	1/2/2016 0:00:00
032016	CRC	-0,011	1/3/2016 0:00:00
042016	CRC	-0,009	1/4/2016 0:00:00
052016	CRC	-0,004	1/5/2016 0:00:00
062016	CRC	-0,009	1/6/2016 0:00:00
072016	CRC	0,005	1/7/2016 0:00:00
082016	CRC	0,006	1/8/2016 0:00:00
092016	CRC	0,004	1/9/2016 0:00:00
102016	CRC	0,014	1/10/2016 0:00:00
012016	DOM	0,025	1/1/2016 0:00:00
022016	DOM	0,017	1/2/2016 0:00:00
032016	DOM	0,016	1/3/2016 0:00:00
042016	DOM	0,018	1/4/2016 0:00:00
052016	DOM	0,017	1/5/2016 0:00:00
062016	DOM	0,019	1/6/2016 0:00:00
072016	DOM	0,019	1/7/2016 0:00:00
082016	DOM	0,015	1/8/2016 0:00:00
092016	DOM	0,014	1/9/2016 0:00:00
102016	DOM	0,009	1/10/2016 0:00:00
012016	ECU	0,031	1/1/2016 0:00:00
022016	ECU	0,026	1/2/2016 0:00:00
032016	ECU	0,023	1/3/2016 0:00:00
042016	ECU	0,018	1/4/2016 0:00:00
052016	ECU	0,016	1/5/2016 0:00:00
062016	ECU	0,016	1/6/2016 0:00:00
072016	ECU	0,016	1/7/2016 0:00:00
082016	ECU	0,014	1/8/2016 0:00:00
092016	ECU	0,013	1/9/2016 0:00:00
102016	ECU	0,013	1/10/2016 0:00:00
012016	GUA	0,044	1/1/2016 0:00:00
022016	GUA	0,043	1/2/2016 0:00:00
032016	GUA	0,043	1/3/2016 0:00:00
042016	GUA	0,041	1/4/2016 0:00:00
052016	GUA	0,043	1/5/2016 0:00:00
062016	GUA	0,044	1/6/2016 0:00:00
072016	GUA	0,046	1/7/2016 0:00:00
082016	GUA	0,047	1/8/2016 0:00:00
092016	GUA	0,045	1/9/2016 0:00:00
102016	GUA	0,047	1/10/2016 0:00:00
012016	HON	0,031	1/1/2016 0:00:00
022016	HON	0,029	1/2/2016 0:00:00
032016	HON	0,025	1/3/2016 0:00:00
042016	HON	0,024	1/4/2016 0:00:00
052016	HON	0,023	1/5/2016 0:00:00
062016	HON	0,024	1/6/2016 0:00:00
072016	HON	0,024	1/7/2016 0:00:00
082016	HON	0,025	1/8/2016 0:00:00
092016	HON	0,029	1/9/2016 0:00:00
102016	HON	0,03	1/10/2016 0:00:00
012016	MEX	0,026	1/1/2016 0:00:00
022016	MEX	0,029	1/2/2016 0:00:00
032016	MEX	0,026	1/3/2016 0:00:00
042016	MEX	0,025	1/4/2016 0:00:00
052016	MEX	0,035	1/5/2016 0:00:00
062016	MEX	0,035	1/6/2016 0:00:00
072016	MEX	0,036	1/7/2016 0:00:00
082016	MEX	0,036	1/8/2016 0:00:00
092016	MEX	0,039	1/9/2016 0:00:00
102016	MEX	0,04	1/10/2016 0:00:00
012016	NIC	0,032	1/1/2016 0:00:00
022016	NIC	0,034	1/2/2016 0:00:00
032016	NIC	0,037	1/3/2016 0:00:00
042016	NIC	0,036	1/4/2016 0:00:00
052016	NIC	0,037	1/5/2016 0:00:00
062016	NIC	0,038	1/6/2016 0:00:00
072016	NIC	0,043	1/7/2016 0:00:00
082016	NIC	0,04	1/8/2016 0:00:00
092016	NIC	0,037	1/9/2016 0:00:00
102016	NIC	0,036	1/10/2016 0:00:00
012016	PAN	0,003	1/1/2016 0:00:00
022016	PAN	0,012	1/2/2016 0:00:00
032016	PAN	0,014	1/3/2016 0:00:00
042016	PAN	0,015	1/4/2016 0:00:00
052016	PAN	0,013	1/5/2016 0:00:00
062016	PAN	0,012	1/6/2016 0:00:00
072016	PAN	0,012	1/7/2016 0:00:00
082016	PAN	0,017	1/8/2016 0:00:00
092016	PAN	0,022	1/9/2016 0:00:00
102016	PAN	0,024	1/10/2016 0:00:00
012016	PAR	0,052	1/1/2016 0:00:00
022016	PAR	0,052	1/2/2016 0:00:00
032016	PAR	0,047	1/3/2016 0:00:00
042016	PAR	0,045	1/4/2016 0:00:00
052016	PAR	0,035	1/5/2016 0:00:00
062016	PAR	0,048	1/6/2016 0:00:00
072016	PAR	0,029	1/7/2016 0:00:00
082016	PAR	0,032	1/8/2016 0:00:00
092016	PAR	0,035	1/9/2016 0:00:00
102016	PAR	0,035	1/10/2016 0:00:00
012016	PER	0,044	1/1/2016 0:00:00
022016	PER	0,042	1/2/2016 0:00:00
032016	PER	0,041	1/3/2016 0:00:00
042016	PER	0,038	1/4/2016 0:00:00
052016	PER	0,035	1/5/2016 0:00:00
062016	PER	0,034	1/6/2016 0:00:00
072016	PER	0,031	1/7/2016 0:00:00
082016	PER	0,03	1/8/2016 0:00:00
092016	PER	0,032	1/9/2016 0:00:00
102016	PER	0,032	1/10/2016 0:00:00
012016	SAL	0,018	1/1/2016 0:00:00
022016	SAL	0,016	1/2/2016 0:00:00
032016	SAL	0,01	1/3/2016 0:00:00
042016	SAL	0,008	1/4/2016 0:00:00
052016	SAL	0,006	1/5/2016 0:00:00
062016	SAL	0,008	1/6/2016 0:00:00
072016	SAL	0,008	1/7/2016 0:00:00
082016	SAL	0,009	1/8/2016 0:00:00
092016	SAL	0,01	1/9/2016 0:00:00
102016	SAL	-0,011	1/10/2016 0:00:00
012016	URU	0,097	1/1/2016 0:00:00
022016	URU	0,102	1/2/2016 0:00:00
032016	URU	0,106	1/3/2016 0:00:00
042016	URU	0,105	1/4/2016 0:00:00
052016	URU	0,11	1/5/2016 0:00:00
062016	URU	0,11	1/6/2016 0:00:00
072016	URU	0,101	1/7/2016 0:00:00
082016	URU	0,094	1/8/2016 0:00:00
092016	URU	0,089	1/9/2016 0:00:00
102016	URU	0,085	1/10/2016 0:00:00
012016	VEN	2,211	1/1/2016 0:00:00
022016	VEN	2,771	1/2/2016 0:00:00
032016	VEN	3,408	1/3/2016 0:00:00
042016	VEN	4,104	1/4/2016 0:00:00
052016	VEN	4,822	1/5/2016 0:00:00
062016	VEN	5,532	1/6/2016 0:00:00
072016	VEN	6,263	1/7/2016 0:00:00
082016	VEN	6,961	1/8/2016 0:00:00
092016	VEN	7,797	1/9/2016 0:00:00
102016	VEN	8,729	1/10/2016 0:00:00H

Here are some pics of my model:

And here is a pic of the error:

Please, somebody help me with this!

Thanks in advance.

KHorseman · ‎03-21-2017

@AnonymousI would use FIRSTNONBLANK(Tablename[Columnname], 1) instead of VALUES(Tablename[Columnname]).

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

View solution in original post

nayan28apr · ‎10-12-2018

Hi,

@Greg_Deckler Need your help:)

I am also facing similar type of issue.

MdxScript(Model) (252, 4) Calculation error in measure 'Callback-SGCIR'[MeasureAvg]: A table of multiple values was supplied where a single value was expected.
Please try again later or contact support. If you contact support, please provide these details.
Show technical details

I have created below summary table from main table using summarize.

UnitSummary = SUMMARIZE(MonthlyRoutecontractUnitSummary,
MonthlyRoutecontractUnitSummary[Year Monthnumber],MonthlyRoutecontractUnitSummary[ActiveUnits],MonthlyRoutecontractUnitSummary[RebelUnits])

Year Monthnumber	ActiveUnits	RebelUnits
2018-09	5759	223
2018-08	5704	277
2018-07	5735	274
2018-06	5714	254
2018-05	5699	246
2018-04	5711	249
2018-03	5703	230
2018-02	5700	245
2018-01	5710	234
2017-12	5717	241
2017-11	5679	235
2017-10	5666	231
2017-09	5580	225
2017-08	5454	233
2017-07	5159	234
2017-06	5166	230
2017-05	5132	218
2017-04	5120	224
2017-03	5082	204
2017-02	5047	234
2017-01	5032	228
2016-12	5131	222
2016-11	5133	199
2016-10	5124	219
2016-09	5150	224
2016-08	5134	243
2016-07	5116	249
2016-06	5189	264
2016-05	5196	257
2016-04	5186	234
2016-03	5186	225
2016-02	5180	209
2016-01	5183	219

Issue seems to be with below formulas, which calculate average of active units from above tables column. I have date table and unit summary above table, which has relationship which i had used.

Units Active 3month Avg = CALCULATE(
AVERAGE(UnitSummary[ActiveUnits]),
USERELATIONSHIP(DateTable[Year Monthnumber],UnitSummary[Year Monthnumber]),
DATESBETWEEN(DateTable[Date],[StartofPreviousQuarter],[EndofPreviousMonth])
)

Same formulas for 6 months, 12 months even this year or past year by passing different dates in datesbetween function above. All are working fine in desktop as below. Added all in one table as below-

Year Monthnumber	ActiveUnits	RebelUnits	Units Active ThisYear Avg	Units Active LastYear Avg	Units Active 12month Avg	Units Active 6month Avg	Units Active 3month Avg
2018-09	5759	223	640		5759	5759	5759
2018-08	5704	277	634		5704	5704	5704
2018-07	5735	274	637		5735	5735	5735
2018-06	5714	254	635		5714	5714
2018-05	5699	246	633		5699	5699
2018-04	5711	249	635		5711	5711
2018-03	5703	230	634		5703
2018-02	5700	245	633		5700
2018-01	5710	234	634		5710
2017-12	5717	241		476	5717
2017-11	5679	235		473	5679
2017-10	5666	231		472	5666
2017-09	5580	225		465
2017-08	5454	233		455
2017-07	5159	234		430
2017-06	5166	230		431
2017-05	5132	218		428
2017-04	5120	224		427
2017-03	5082	204		424
2017-02	5047	234		421
2017-01	5032	228		419

But when my on premise datasets are refreshed with the help of gateway, this formulas returning blank in the service. I have checked by edit report.

Kindly advise.

Thanks

KHorseman · ‎03-21-2017

LEFT() takes a single scalar text value. VALUES() returns a one-column table of multiple values. You're trying to feed a table into a slot that only fits a single text value.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Anonymous · ‎03-21-2017

Thanks @KHorseman for your reply.

Any suggestions to perform this?

Thanks for your help.

KHorseman · ‎03-21-2017

@AnonymousI would use FIRSTNONBLANK(Tablename[Columnname], 1) instead of VALUES(Tablename[Columnname]).

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

coolshrawan · ‎02-03-2022

In the case the first non blank is incorrect result, what should be done. let's say if the output values are "Completed", "InProgress", "Completed" and as there is inprogress in one of the value, i want it to have "Inprogress" in the output and not the first "completed" one.

alonb · ‎10-14-2017

Hey,

Why not use this function (Values)?

BR

Alon

KHorseman · ‎10-18-2017

@alonb VALUES will return multiple values unless the structure of your data guarantees that only a single value will be supplied every time. If the formula sees a column of multiple values, it will return that with VALUES. FIRSTNONBLANK forces it to only take the first single row value in all cases.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

bhavana097 · ‎07-27-2022

Yes, FIRSTNONBLANK solved the issue, however, it is bringing up the only first value that is matching up rather than bringing the entire value in the table

Anonymous · ‎03-22-2017

Thanks @KHorseman.

FIRSTNONBLANK solved this!

Have a great day.

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

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly