Forum Discussion

plukacsi's avatar
plukacsi
Icon for Helper I rankHelper I
8 years ago

Multiple Currencies, Currency Conversion with SUMX/LOOKUPVALUE

 

Dear All,

I need help with this structure beacuse I cannot select a currency rate with the help of SUMX and LOOUPVALUE. I get NaN error message  and I also cannot filterDimExRates_unpivoted with DimDatePeriods normally. I have got values only for P10,P11 and P12.

 

Thank you for your help in advance!

 

BR,

Peter

Need Help

5 Replies

  • Yggdrasill's avatar
    Yggdrasill
    Icon for Responsive Resident rankResponsive Resident
    8 years ago

    Create a relationship from your exchange rate table to your dimDate table

     

    Then do the following in DAX as MEASURE

     

    AmountInCurrencyX =
SUMX ( factTable ; factTable[Amount] * RELATED ( currencyTable[ExchangeRateColumn] )

     

     

     

    • plukacsi's avatar
      plukacsi
      Icon for Helper I rankHelper I
      8 years ago

      Thank you, but the problem is that I have days in DimDate and I have periods(month) in exchange rate table, that is why I have created the help table between them with uniqe value for periods, so I cannot create relationship between DimDate(days, month with multiple values) and ExchangeRate_unpivoted(month with multiple values)

      • Yggdrasill's avatar
        Yggdrasill
        Icon for Responsive Resident rankResponsive Resident
        8 years ago

        Double click the relationship line between the period table and exchange rate table. Select Both from Cross filter direction and OK

         

        I'm not sure this work but give it a try ?