Forum Discussion

Rdarshana's avatar
Rdarshana
Icon for Helper II rankHelper II
1 year ago

Currency Conversion Data Model

Hi, I wanted to run the data model currency conversion for the group out here. This is what I have. The dimensions are all Dual Mode and Fact tables are Direct Query. Exchange Rates are ...
Need Help
Tips and Tricks
Rdarshana's avatar
Rdarshana
Icon for Helper II rankHelper II
1 year ago

Hi Neeko,
I looked at the DAX calc too, but there seems to be an issue with the model.

The relationship between Exchange Rate table and Fact 2 and Fact Table 1 tables is inactive as PBI identifies it as many-to-many relationship and presence of ambiguous relationships as Exchange rate table and Calendar tables are both joined to the fact tables. 

So, let's say, we decide not to join the Exchange Rate table at all. 
I am referencing this link - https://www.youtube.com/watch?v=HlR-AyzctLY

I can create a measure using LookUp function - 
** I will have a currency slicer
** I have a calendar date slicer . The max date on this slicer should give me the first of month for that date.
and this first of month should give me the exchange rate

Measure = 

VAR CurrencySelected = SELECTEDVALUE(ExchangeRate[To Currency])  ** This will be the currency slicer**
VAR RateDate = LASTDATE('Calendar'[Date])                               ** This is max date from slicer; I will get the first of month from this date and calc the rate from the exchange rate table based on first of month**
 
VAR RECENTRATE =
LOOKUPVALUE(monthly_average_exchange_rate[Rate],
monthly_average_exchange_rate[To Currency],CurrencySelected,
monthly_average_exchange_rate[Rate Date],STARTOFMONTH(RateDate))
RETURN
IF(ISBLANK([Total Software Spend]*RECENTRATE),[Total Software Spend],[Total Software Spend]*RECENTRATE)




The above DAX query works when I select the ToCurrency Code to be anything else other than USD. 
If I select USD, then it gives me an error stating - "Couldnt load data for this visual. A table of multiple values was supplied where a single value was expected"

I added the IsBlank condition just to ensure that the data will show the original amount as is in the New amount field. 
Is there anything amiss?