Forum Discussion
Native Query brings all data in tabular visual when currency conversion is applied.
All,
I wanted to inform that the issue was resolved.
A Date bridge table containing column with the first of month was added.
This date bridge table was joined to the Exchange Rate Table via a many-to-one join.
The date bridge table is then joined to the fact table via a one to many join.
The DAX query was then updated accordingly, and this resolved the issue.
Hello, Rdarshana ,
I don't like the Many To Many, especially if it's set up to both (direction),
I would consider changing the Rate Table and generate some surrogate key (SK), so you can join your fact table with One To Many (exchance -> fatct).
Perhaps from the date range, you can create row for each date and add a valid currency to it and create the SK from it, same way then in your fact table. Perhaps that may help.
vojtechsima
I created two bridge tables between the monthly exchange rate table and the fact table.
1. DistinctRateDates table (Joined to Exchange Rate table - one to many join on Rate Date)DistinctRateDates =DISTINCT(SELECTCOLUMNS('monthly_average_exchange_rate', "Rate Date", 'monthly_average_exchange_rate'[Rate Date]))
2. DateBridgeTable (Joined to DistinctRateDates table - many to one ; Joined to Fact table - one to many)DateToRateBridge =SELECTCOLUMNS ('Spend Calendar',"Date", 'Spend Calendar'[Date],"Rate Date", DATE(YEAR('Spend Calendar'[Date]), MONTH('Spend Calendar'[Date]), 1))
After this, I applied the monthly calculation for currency conversion. The logic works, but the moment I add the ID field, it just blows up with the 1 millions records error message.
The ID field is a must to the tabular visual.
Is there anything else that could be looked into?