Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Currency Conversion Power Bi

Hi All,   This might be a very basic question to many of you. I am trying to create a model where all my transactions carried out in different currencies is translated into USD using montly exchang...
  • johnt75's avatar
    johnt75
    3 years ago

    You could do it as a measure like

    USD Amount Measure =
SUMX (
    'Transactions',
    VAR CurrencyRate =
        LOOKUPVALUE (
            'Exch rate'[rate],
            'Exch Rate'[Date],
                DATE ( YEAR ( 'Transactions'[Transaction date] ), MONTH ( 'Transactions'[Transaction date] ), 1 ),
            'Exch rate'[Currency], 'Transactions'[Currency]
        )
    RETURN
        'Transactions'[Amount] * CurrencyRate
)

    but I think performance would likely be better having a calculated column and then building measures based on that. If it was a column then it would be calculated once, during data refresh, and then you could have measures to perform sum, average etc on the calculated column without having to pay the cost of all the lookups.

    Best advice is to try both ways and use DAX Studio to compare performance

johnt75's avatar
johnt75
Super User
3 years ago

You could create a calculated column in the transactions table like

USD Amount =
VAR CurrencyRate =
    LOOKUPVALUE (
        'Exch rate'[rate],
        'Exch Rate'[Date],
            DATE ( YEAR ( 'Transactions'[Transaction date] ), MONTH ( 'Transactions'[Transaction date] ), 1 ),
        'Exch rate'[Currency], 'Transactions'[Currency]
    )
RETURN
    'Transactions'[Amount] * CurrencyRate
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi John,

     

    This did work. But i was wondering if instead of adding conditional column, there was a way to achieve this through dax measures.

     

    Since i intend to add more factual transaction table and as far as i know measures are way better in terms of performance than calculated columns.

     

    • johnt75's avatar
      johnt75
      Super User
      3 years ago

      You could do it as a measure like

      USD Amount Measure =
SUMX (
    'Transactions',
    VAR CurrencyRate =
        LOOKUPVALUE (
            'Exch rate'[rate],
            'Exch Rate'[Date],
                DATE ( YEAR ( 'Transactions'[Transaction date] ), MONTH ( 'Transactions'[Transaction date] ), 1 ),
            'Exch rate'[Currency], 'Transactions'[Currency]
        )
    RETURN
        'Transactions'[Amount] * CurrencyRate
)

      but I think performance would likely be better having a calculated column and then building measures based on that. If it was a column then it would be calculated once, during data refresh, and then you could have measures to perform sum, average etc on the calculated column without having to pay the cost of all the lookups.

      Best advice is to try both ways and use DAX Studio to compare performance