Currency Conversion with Mixed Mode (FACT in DQ and DIm in import mode)

[Anonymous]

Hi Community,

How we can relate import mode table with Directquery Table.

I have below fact table which is in direct query.

Account_key | Amount | LocalCurrency

1| 10 | USD

1| 11.3 | EUR

1| 741| INR 
2 | 128.1 | INR

I have Below Dimension Table which is also in Directquery Mode

Account_key |  Account Name

1| Acc1

2| Acc2

I have Exchange Rate table which has excel source and it is in Import mode.

Currency | Rate for 1 USD

USD| 1.0

INR | 74.05

EUR | 1.13

Account and Exchange rate table are coonected with fact in 1 to many Relation. So i want to calculate amount in USD per account.

Expected Output

Account name | USD Amount

Account 1 | 30

Account 2 | 2

Thanks,

Pravin

[Anonymous]

Hi @Anonymous ,

Create relationships between these three tables and it should work.

Best Regards,

Jay

[Anonymous]

Hi,

I have created relationship but when i am using it in calculation it is giving me wrong result.

My table visual contains only account column and measure. I suspect this behaviour could be because of row context, Since one account corresponds to multiple local currencies.

Thanks,

Pravin

[lbendlin]

"Since one account corresponds to multiple local currencies"

Please explain.  Do you mean different monthly rates (in which case you need a CurrencyMonth key) or transactions by the same customer in the same month with different currencies?

[lbendlin]

That should work. What have you tried, where are you stuck?