currency conversions using daily rates - now PBI is super slooooooow

[Karimthedream]

Hi,

im pretty new to PBI and i am struggling with conversion rates.

in summary:

• we have orders that come into a conversions table, these orders can be in any currency (one column for the currency, one column for the amount)
• we then have a commission amount associated with each order, this too can be in any currency (again one column for the currency, a second column for the amount)
• the order currency and the commission currency can be different for the same order

i would like to be able to convert both the order currency and the commission currency to GBP based on the exchange rate of the order date

My solution so far:

i have brought in a currency exchange rate table for all currencies to GBP, which provides a rate for each day, an extract of the table is below

i then created a custom column to work out the exchange rate based on the order currency and the date of the order:

Order Amount Exchange Rate =

VAR OrderCurrency = 'Conversions & CPC Tables & PublisherConversions (Partners DB)'[OrderAmount_CurrencyCode]

VAR OrderDate = 'Conversions & CPC Tables & PublisherConversions (Partners DB)'[CreatedAt - DATE ONLY]

VAR ExchangeRate =

    CALCULATE(

        MIN('Exchange Rates'[Rate]),

        FILTER('Exchange Rates', 'Exchange Rates'[Currency] = OrderCurrency

            && 'Exchange Rates'[Date] = OrderDate)

    )

RETURN ExchangeRate

and then a second column to do the division:

Order Amount GBP = 'Conversions & CPC Tables & PublisherConversions (Partners DB)'[OrderAmount_Amount]/'Conversions & CPC Tables & PublisherConversions (Partners DB)'[Order Amount Exchange Rate]

although this solution works, the whole report is incredibly slow (both in the desktop and cloud) compared to the solution i used previously, which was just using a conditional column to set a desired exchange rate based on the order currency, then dividing one column by the other. however i am after a solution that uses live exchange rates so i can use the data for financial reporting.

is there a more efficient way to do this? 

thanks for your help

[Karimthedream · Accepted solution]

Hi,

i managed to resolve the issue. It wasnt the currency conversions that was slowing down the report, it was the fact that i had a lot of different relationships between tables/databases. i managed to reduce the number of relationships by using the lookup function with dax to essentially add data from one table to another, that way when building visuals, all the data was coming from 1 table, rather than various tables via relationships.

Thanks

[lbendlin]

i have brought in a currency exchange rate table for all currencies to GBP, which provides a rate for each day,

that may be too much data. How many currencies are you tracking?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Karimthedream]

Hi, thanks for getting back to me.

Currently i am allowing for any currency worldwide, since thats the data we are currently receiving. the URL below will take you to a file with some dummy data, essentially each of the columns with an amount would need to be converted into GBP, based on the exchange rate of the day of that row. with each of the columns potentially being in a different currency. any help would be greatly appreciatated.

thanks 

Dummy Data file for PBI 

[lbendlin]

Your data model is missing the Calendar table.