PowerBI - "Original" currency display not working, other currency fine

CNH

Upon refreshing my PowerBI model I get the following error message

Error Message:
MdxScript(Model) (89, 187) Calculation error in measure 'Report'[Equity Value]: A table of multiple values was supplied where a single value was expected.

When I look at specific currencies there is no problem.

VidasMatelis

Hi,

It appears that you have quotes for 3 symbols that are not defined in the symbol table. But you need to find these symbols. In a few hours I will go on vacation, so I do not have more time to investigate.
So find quotes for symbols that are not defined and either remove quotes or add symbols to Symbol table.
I coded on purpose to fail refresh, but my attempt was to prevent refresh when there are duplicate quotes. As a side effect, when there are undefined symbols, then refresh fails too.

Simple fix is also to change formula.

In the release Report[Equity Value] formula is:

Equity Value = ROUND(SUMX(VALUES(Symbol[Symbol])
			, ROUND(
					CALCULATE(SUM(Trans[QtyChange])
					, DATESBETWEEN(Dates[Date], BLANK(), MAX(Dates[Date])), All(Dates)
					, ALL(TransType), TransType[QtySign]<>0
					)
				,3)
	
			  * IF(ISFILTERED(SymbolSector[Sector]) || ISFILTERED(SymbolSector[Sensitivity]), CALCULATE(SUM(SymbolSector[Percent])), 1)
			  * CALCULATE(
					IF(NOT(HasOneValue(ReportCurrency[CurrencyID])), VALUES(Quotes[Close])
						, SWITCH(VALUES(ReportCurrency[CurrencyID]), 1, VALUES(Quotes[Price1]), 2, VALUES(Quotes[Price2]), 3, VALUES(Quotes[Price3]), 4, VALUES(Quotes[Price4]), 5, VALUES(Quotes[Price5]), VALUES(Quotes[Close]) )
					)
					, LASTNONBLANK(
						DATESBETWEEN(Dates[Date], DateAdd(LastDate(Dates[Date]), -40, day), MAX(Dates[Date]))
					  , CALCULATE(COUNT(Quotes[Close]) )) 
				)
		)
, 2)

I changed that to:

Equity Value = ROUND(SUMX(VALUES(Symbol[Symbol])
			, ROUND(
					CALCULATE(SUM(Trans[QtyChange])
					, DATESBETWEEN(Dates[Date], BLANK(), MAX(Dates[Date])), All(Dates)
					, ALL(TransType), TransType[QtySign]<>0
					)
				,3)
	
			  * IF(ISFILTERED(SymbolSector[Sector]) || ISFILTERED(SymbolSector[Sensitivity]), CALCULATE(SUM(SymbolSector[Percent])), 1)
			  * CALCULATE(
					IF(NOT(HasOneValue(ReportCurrency[CurrencyID])), VALUES(Quotes[Close])
						, SWITCH(VALUES(ReportCurrency[CurrencyID]), 1, SUM(Quotes[Price1]), 2, SUM(Quotes[Price2]), 3, SUM(Quotes[Price3]), 4, SUM(Quotes[Price4]), 5, SUM(Quotes[Price5]), SUM(Quotes[Close]) )
					)
					, LASTNONBLANK(
						DATESBETWEEN(Dates[Date], DateAdd(LastDate(Dates[Date]), -40, day), MAX(Dates[Date]))
					  , CALCULATE(COUNT(Quotes[Close]) )) 
				)
		)
, 2)

I will send you back PowerBI desktop with this change. Basically, all I fix is change VALUES to SUM in the place that returns last price.

CNH

Hi Vidas, I will look for the undefined symbols. Thank you for your effort, it is much appreciated