Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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:

 

Sin título.pngSin título2.png

 

 

And here is a pic of the error:

Sin título3.png

 

 

Please, somebody help me with this!

 

Thanks in advance.

1 ACCEPTED SOLUTION

@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

9 REPLIES 9
nayan28apr
Helper I
Helper I

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 MonthnumberActiveUnitsRebelUnits
2018-095759223
2018-085704277
2018-075735274
2018-065714254
2018-055699246
2018-045711249
2018-035703230
2018-025700245
2018-015710234
2017-125717241
2017-115679235
2017-105666231
2017-095580225
2017-085454233
2017-075159234
2017-065166230
2017-055132218
2017-045120224
2017-035082204
2017-025047234
2017-015032228
2016-125131222
2016-115133199
2016-105124219
2016-095150224
2016-085134243
2016-075116249
2016-065189264
2016-055196257
2016-045186234
2016-035186225
2016-025180209
2016-015183219

 

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 MonthnumberActiveUnitsRebelUnitsUnits Active ThisYear AvgUnits Active LastYear AvgUnits Active 12month AvgUnits Active 6month AvgUnits Active 3month Avg
2018-095759223640 575957595759
2018-085704277634 570457045704
2018-075735274637 573557355735
2018-065714254635 57145714 
2018-055699246633 56995699 
2018-045711249635 57115711 
2018-035703230634 5703  
2018-025700245633 5700  
2018-015710234634 5710  
2017-125717241 4765717  
2017-115679235 4735679  
2017-105666231 4725666  
2017-095580225 465   
2017-085454233 455   
2017-075159234 430   
2017-065166230 431   
2017-055132218 428   
2017-045120224 427   
2017-035082204 424   
2017-025047234 421   
2017-015032228 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. 

 

issue con.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Kindly advise.

 

Thanks

KHorseman
Community Champion
Community Champion

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
Not applicable

Thanks @KHorseman for your reply.

 

Any suggestions to perform this?

 

Thanks for your help.

@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!




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.

Hey, 

 

Why not use this function (Values)?

 

BR

Alon

KHorseman
Community Champion
Community Champion

@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!




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
Not applicable

Thanks @KHorseman.

 

FIRSTNONBLANK solved this!

 

Have a great day.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.