cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Magellan2022
Regular Visitor

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)

Curr Conv - SSAS Model.PNG

(DAX measure)

Measure - Currency Conversion DAX Logic.PNG

2 ACCEPTED SOLUTIONS
bcdobbs
Super User
Super User

I wonder if you could use TREATAS to pass the filter inside a calculate. Should be faster than FILTER.

 

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

@Magellan2022 This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

4 REPLIES 4
Magellan2022
Regular Visitor

@Greg_Deckler Hi Greg, thought maybe you could share some possible insight into this behavior with TREATAS? 

bcdobbs
Super User
Super User

I wonder if you could use TREATAS to pass the filter inside a calculate. Should be faster than FILTER.

 

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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.

 

TREATAS - No Totals Value.PNGTREATAS Solution.PNG

 

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)

TREATAS Solution - No Filter.PNG

(_CURRENCY_REVENUE = GBP)

TREATAS Solution - GBP Filter.PNG

 

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.

TREATAS Solution - Removed Calendar ID.PNG

@Magellan2022 This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!