Poor performance of SUMX and Calculate measure

[Nikolainoergard]

Hi Community

After computing some custom time intelligence measures my report performance went really bad. Due to not following a Gregorian calendar i have based my measures on the article by SQLBI gents: https://www.daxpatterns.com/custom-time-related-calculations/

Since I'm utilizing their measures, I suspect that the performance issues may stem from my general Sales measure, which is not straightforward due to the need to incorporate currency conversion. I attempted to analyze this using DAX Studio, but I believe my limited DAX knowledge is hindering the solution.

Here is my Sales measure DAX expression:

Sales =

SUMX (

    'FactSales',

    'FactSales'[Revenue]

        * CALCULATE (

            FIRSTNONBLANK ( 'DimCurrency'[Currency Rate], 1 ),

            USERELATIONSHIP ( 'FactSales'[CurrencyKey], 'DimCurrency'[CurrencyKey] )

        )

)

I have a Many-to-Many relationship between FactSales and DimCurrency, and the relevant columns are as follows:

Currency Name	Currency	CurrencyYear	FromCurrency	Currency Rate	CurrencyKey
Swiss Franc	CHF	2021	ALL	0,008601457	2021ALL
Swiss Franc	CHF	2021	CNY	0,138102169	2021CNY
Swiss Franc	CHF	2021	MXN	0,045118218	2021MXN
Swiss Franc	CHF	2021	QAR	0,265412088	2021QAR

I'm unsure whether using SUMX is the most optimal approach and how to best apply the Currency Rate in this scenario..

Thanks in advance

[_elbpower · Accepted solution]

Considering the data set you provided, it appears to be many-to-one. To illustrate a many-to-many relationship, we can extend the example with more data. Let's also clarify whether the 'DimCurrency' table represents a time-dependent or pegged exchange rate structure for the current year.

If we want to demonstrate a many-to-many relationship, we might introduce additional columns and data points in both 'FactSales' and 'DimCurrency' tables. We could add a 'TransactionDate' column to 'FactSales' to represent when the transactions occurred and associate these dates with different exchange rates in 'DimCurrency.' This would illustrate a situation where there could be multiple exchange rates for different dates in 'DimCurrency.'

Regarding 'DimCurrency,' it depends on your specific business requirements. If it's meant to represent time-dependent exchange rates, you would have a different exchange rate for each currency for each time period (e.g., days, months). If it's pegged to the current year, you might only have one exchange rate for each currency for the entire year.

Additionally, you can create a calculated group to handle the currency conversion logic:

1. Retrieve the selected reporting currency, which corresponds to the currency column in the 'DimCurrency' table.
2. Multiply the selected measure by the exchange rate selected from 'DimCurrency,' filtering by 'Currency' with the 'SelectedReportingCurrency.' Ensure there is an active relationship between the fact and dim tables.

Here's how you can create the calculated group for currency conversion:

CurrencyConversion = 
VAR SelectedReportingCurrency = SELECTEDVALUE('DimCurrency'[Currency])
RETURN
    SUMX(
        FILTER(
            'DimCurrency',
            'DimCurrency'[Currency] = SelectedReportingCurrency
        ),
        CALCULATE(
            SELECTEDMEASURE() * MIN('DimCurrency'[ExchangeRate]) -- You can use Max as well, it needs to return one value related to the fact
        )
    )

Lastly, apply this calculated group to your 'FactSales' measure to calculate the converted value:

Fact Sales = CALCULATE([Sales], 'CalculatedGroupName'[Option] = "CurrencyConversion")

This approach uses the "CurrencyConversion" calculated item within the "CalculatedGroupName" calculated group to convert your 'FactSales' measure into the selected reporting currency. Please adapt the actual table and column names to match your specific data model.

[Nikolainoergard]

Hi @_elbpower Thanks for your detailed answer

The relationship between FactSales and DimCurrency is Many-to-Many, as it is required to be able to convert to multiple currencies. See below:

DimCurrency' represents a yearly pegged exchange rate structure.

In this case, what solution do you think would be the most optimal? 

Again, thanks a lot - really appricate it

[_elbpower]

Certainly, the proposed solution remains effective regardless of whether you have a many-to-one or many-to-many relationship in your data model.

The approach involving a slicer that leverages the 'DimCurrency[Currency]' column in your report allows users to choose their desired reporting currency. This practice streamlines the process, reducing unnecessary operations, and dynamically adapts data to the user's currency selection. It ensures that users can view data in their preferred currency, promoting a more user-friendly experience.

If your aim is to display converted values based on the user's choice (one currency at a time), this slicer-based method is well-suited. Users can simply pick a currency from the slicer, and the measures will automatically recalculate values in the chosen currency. This approach caters to user preferences without cluttering the report with multiple conversions simultaneously.

Moreover, it's worth noting that the calculated group approach remains robust, even in scenarios with a many-to-many relationship. While many-to-many relationships may introduce additional intricacies related to intermediary tables or filter propagation, the calculated group method still offers a solution that abstracts the complexity of currency conversion logic. It seamlessly aligns with the relationships you've established in your data model, ensuring that your measures correctly adjust to the user's selected reporting currency, irrespective of the specific relationship type in place.

[sjoerdvn]

All "X" functions should be used with care, as they don't scale well. Iterating over an entire fact table is always a bad idea. I had a simular issue years ago, also with currencies and a fact table without a straightforward relationship with currency rates.
You are probably better of doing this:

• create a simple currency dimension with only the currency code, name, description (dimCurrency)
• save the rates in a different table  (factCurrencyRates)
• Create relationships from dimCurrency to both factSalaes and factCurrencyRates
• Your measure should start with SUMX(VALUES(dimCurrency[CurrencyCode]), CALCULATE(SUM(FacctSales) ......

Iterating over  hand full of currencies will not e an issue.