Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
(Model)
(DAX measure)
Solved! Go to Solution.
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/
@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
@Greg_Deckler Hi Greg, thought maybe you could share some possible insight into this behavior with TREATAS?
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/
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.
@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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |