none
Currency Conversion on the fly in Tabular Model

    Question

  • Hi,

    I'm implementing a currency conversion in a tabular model. It works, however it is too slow.

    I have a "Report Currency"-table with date,[currency code] and rate columns, where the rate is the Exchange rate between the currency and my base currency (EUR).

    In my fact table I have an order date that determines which rate to use.

    I show the currency column in the client Tools (i.e. Excel). The user then filters the currency code for example choosing "USD" as currency. The measure, for example "Line Amount (Report Currency)" is then shown in USD.

    I have tried the following DAX-expressions for the measure:

    Line Amount (Report Currency):=IF(
       HASONEVALUE('Report Currency'[Currency Code]);
    CALCULATE(
         SUMX('Purchase Order';[Line Amount SEK]* CALCULATE(VALUES('Report Currency'[Rate]);FILTER('Report Currency';'Purchase Order'[OrderDate] = 'Report Currency'[Date] )))
           ;FILTER('Report Currency';FILTERS('Report Currency'[Currency Code])='Report Currency'[Currency Code]    ))
        ;
        BLANK()
    )

    and also using LOOKUPVALUE:

    Line Amount (Report Currency):=IF(
       HASONEVALUE('Report Currency'[Currency Code]);
    CALCULATE(
         SUMX('Purchase Order';[Line Amount SEK]* LOOKUPVALUE('Report Currency'[Rate];'Report Currency'[Date];'Purchase Order'[OrderDate];'Report Currency'[Currency Code];FILTERS('Report Currency'[Currency Code])))
           ;FILTER('Report Currency';FILTERS('Report Currency'[Currency Code])='Report Currency'[Currency Code]    ))
        ;
        BLANK()
    )

    If you have any tips on speeding up the measure I would be really happy. Can we get away from the SUMX?

    Thanks!

    /Martin

    Wednesday, August 21, 2013 8:48 AM

All replies

  • As a solution for reducing query time for the most used currency I added a switch statement and if USD is selected the measure "Line Amount (USD)" is used.

    This measure is really quick when used directly (as it is just a sum) but when used in the below formula it is significantly slower. I don't see why? As I see it no row by row calculation is needed.  

    The motivation behind my measure is to get away from having one measure per currency.

    Line Amount (Report Currency):=IF(
       HASONEVALUE('Report Currency'[Currency Code]);SWITCH(FILTERS('Report Currency'[Currency Code]);"USD";[Line Amount (USD)];
    CALCULATE(
         SUMX('Purchase Order';[Line Amount SEK]* CALCULATE(VALUES('Report Currency'[Rate]);FILTER('Report Currency';'Purchase Order'[OrderDate] = 'Report Currency'[Date] )))
           ;FILTER('Report Currency';FILTERS('Report Currency'[Currency Code])='Report Currency'[Currency Code]    ))
        );
        BLANK()
    )

    Thanks again!

    /Martin

    Thursday, August 22, 2013 6:17 AM