April 29, 2013 at 9:33 am
Hi All
I m novice in SSAS.
SSAS 2012 SP1 on W7 x64
I need to be able to convert all sums into reports into different currencies
is good article but
Target Currency Dimension Table (optional)
I did not include this dimension in the tabular model since we only convert to one currency – US Dollars.
If you need to convert to multiple target currencies, then this dimension is required.
I need Multiple target currencies
to switch in excel - and see figures in chosen currency
v_SalesFact - fact table with CurrencyCode and
Sum_pos:=SUMX( v_SalesFact, [Quantity]*[LineAmount] ) - measuse
I made 2-nd table ToCurrency (same as FromCurrency)
and add condition as slicer :, 'ToCurrency'[currency_code] = "EUR"
This runs OK on SSMS and works correct, when only one ToCurrency chosen.
But when I made SAME measure s_look3 in SSDT and try to see it (s_look3) in Browse Cube
I got error :
ERROR - CALCULATION ABORTED: Calculation error in measure 'v_SalesFact'[s_look3]: A table of multiple values was supplied where a single value was expected.
DAX:
DEFINE
MEASURE 'v_SalesFact'[look1]=
if( [Sum_Pos] = 0 , 0,
-- !! here will be ERRROR !!
if( VALUES(v_SalesFact[CurrencyCode]) = VALUES( ToCurrency[currency_code]) , [Sum_pos] -- -33
,
SUMX ( v_SalesFact,
'v_SalesFact'[Sum_pos] * --v_SalesFact[SalesAmount] *
LOOKUPVALUE ( ExchangeRates[Rate]
,ExchangeRates[YearRate], YEAR( v_SalesFact[InvoiceDate] )
,ExchangeRates[MonthRate], MONTH( v_SalesFact[InvoiceDate] )
,ExchangeRates[FromCurrency], v_SalesFact[CurrencyCode]
--,ExchangeRates[ToCurrency] , "USD" -- "USD"--v_SalesFact[CurrencyCode]
,ExchangeRates[ToCurrency] , VALUES( ToCurrency[currency_code] )
)
)
)
)
-----------------------
EVALUATE
CALCULATETABLE(
ADDCOLUMNS
( 'v_SalesFact'
, "S1", 'v_SalesFact'[S1]
, "T_YTD", 'v_SalesFact'[T_YTD]
, "look1", 'v_SalesFact'[look1]
)
,'v_SalesFact'[CustomerID] = 46 || 'v_SalesFact'[CustomerID] = 27 || 'v_SalesFact'[CustomerID] = 1
, 'ToCurrency'[currency_code] = "EUR" -- "SEK" -- "EUR" -- "USD"
)
ORDER BY v_SalesFact[CustomerID] desc , v_SalesFact[InvoiceDate]
I see problem in VALUES(v_SalesFact[CurrencyCode]) =
(when I comment this - work )
but I needed to convert from EUR to EUR as 1 to 1
although there is No rates in ExchangeRates.
I know how to do it in SQL, but not in DAX.
I guess problem is in context - VALUES(v_SalesFact[CurrencyCode]) return many values
but can't guess how to do with it.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply