Forum Discussion

Magellan2022's avatar
Magellan2022
Helper I
4 years ago
Solved

Currency Conversion (Multiple Sources, Multiple Targets) - Fact Table w/ Two Dates, Two Amounts

Hello,

 

I followed the solution listed here...https://www.daxpatterns.com/currency-conversion/...multiple sources, multiple targets as part of currency conversions for our reporting needs. In our model, our fact table has two dates and two amounts as well as the associated currency ISO codes for each of the amounts (see model below). 

 

We thought we could implement the USERELATIONSHIP function in the measure that's performing the currency conversion but found that we can't use the USERELATIONSHIP within the ADDCOLUMNS/SUMMARIZE DAX pattern that's shown in the article link provided above. SSAS Tabular doesn't allow this/errors out. Instead, we had to use the FILTER function in order to get the measure to work and pull the correct currency conversion rate. The measure works(see below for the DAX we implemented) but is slow to render the data which we suspect is a result from the two FILTER functions being used. We have another measure but for the Cost Amount that executes the same DAX logic. Any ideas on how we can improve the performance of the measures?

 

Important Notes:

  • In the fact table, the Currency Revenue Dim Calendar ID and Currency Cost Dim Calendar ID are different and usually never the same IDs/Date values.
  • Currency Cost and Currency Revenue are the fields that contain the currency ISO codes.
  • _Transaction Amt Rev in the DAX below is a measure where the DAX behind it is SUM('Fct Transaction[_AMOUNT_REVENUE])

(Model)

(DAX measure)

Need Help

4 Replies

    • Magellan2022's avatar
      Magellan2022
      Helper I
      4 years ago

      I replaced the DAX FILTERs with the TREATAS functions and I'm getting the expected results at the transactional level but no value is being returned at the Totals level. The Transaction Amt Rev* field contains the FILTER logic and that is returning a value at the the Totals level.

       

       

      UPDATE:

      The above table screenshot is being filtered down to _CURRENCY_REVENUE = 'CAD'. When I remove this filter, a total amount is now being shown but still incorrect. The amount shown is the total only for the records where _CURRENCY_REVENUE = 'GBP' as no currency conversion is actually happening because the target currency to be converted to is GBP. 

       

      (No Filter)

      (_CURRENCY_REVENUE = GBP)

       

      Also, I noticed that when I remove _CURRENCY_REVENUE_DIM_CALENDAR_ID field from the table that some of the values for the z_Amt Rev Curr(contains TREATAS logic) disappear. The values that do remain are for _CURRENCY_REVENUE equal to GBP, which no conversion is happening, or where there is only one transactional record for a given currency ISO code such as with DKK or HUF. Either way there seems to be an impact to the z_Amt Rev Curr value when the granularity is changed to a higher level. Perhaps this is related to the granularity/filter context of the DAX written above in the original post with the ADDCOLUMNS/SUMMARIZE DAX pattern which is at the _CURRENCY_REVENUE_DIM_CALENDAR_ID and _CURRENCY_REVENUE level? I guess I wouldn't expect this to happen as the currency conversion is calculated at the correct level but not able to be aggregated up to a Totals level that I thought would happen through the SUMX.