Tabular) DAX currency conversion multiple target values

• gfv1

  SSC Enthusiast

  Points: 128

  More actions

  April 29, 2013 at 9:33 am

  #293220

  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.