Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tabular) DAX currency conversion multiple target values Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 9:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 6, 2013 7:27 AM
Points: 2, Visits: 23
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

http://www.sqlservercentral.com/blogs/salvoz-sql/2013/01/28/currency-conversion-in-tabular-model-using-dax/#comments
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.


Post #1447611
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse