How to improve DAX query performance - aggregation of multiple currencies

[konradjonsson]

Hi.

Originally I had a DAX measure that gave the correct result on row level, but totals where wrong. See bottom of message.

I had a previous solution where the iteration is at a granular level for each row and then aggregates these results. I knew it was going to be slower, but tried it anyway.

My question to the community is if there are any better (=faster) ways to aggregate correctly on multiple currencies?

Plan (Budget) SLOW =  

SUMX('Plan data', 

    VAR _recordDate = 'Plan data'[DateIntKey] 

    VAR _recordCurrency = 'Plan data'[CurrencyKey] 

    VAR _displayCurrency =

SELECTEDVALUE('Slicer - Targetcurrency'[Currency_BK]) 

    VAR _exchangeRateType = [Exchange Rate Type] 

    VAR _exchangeRate = CALCULATE( 

        MAX('FactExchangeRate'[Rate]), 

        'FactExchangeRate'[Date_SK] = _recordDate, 

        'FactExchangeRate'[FromCurrencyCode] = _recordCurrency, 

        'FactExchangeRate'[ExchangeRateType] = _exchangeRateType, 

        'FactExchangeRate'[ToCurrencyCode] = _displayCurrency 

    ) 

    VAR _valueInRecordCurrency = [Plan (Budget) LOCAL] 

    RETURN 

        _valueInRecordCurrency * _exchangeRate 

) 

The original DAX query - actually two.

Plan (Budget) INCORRECT TOTALS =  

VAR LocalAmount = 

    CALCULATE( 

        SUM('Plan data'[Value]), 

        'Plan data'[ScenarioKey] = "EF", 

        'Plan data'[MeasureKey] = "M2" 

    ) 

RETURN 

    LocalAmount * [Exchange Rate] 

Exchange Rate =  

VAR _date = 

    MAXX ( 'Plan data', 'Plan data'[DateIntKey] ) 

VAR _currency = 

    MAXX ( 

        'Plan data', 

        'Plan data'[CurrencyKey] 

    ) 

VAR _DisplayCurrency = 

    SELECTEDVALUE ( 'Slicer - Targetcurrency'[Currency_BK] ) 

VAR _exchangeratetype = [Exchange Rate Type] 

RETURN 

    CALCULATE ( 

        MAX ( 'FactExchangeRate'[Rate] ), 

        'FactExchangeRate'[Date_SK] = _date, 

        'FactExchangeRate'[FromCurrencyCode] = _currency, 

        'FactExchangeRate'[ExchangeRateType] = _exchangeratetype, 

        'FactExchangeRate'[ToCurrencyCode] = _displaycurrency 

    ) 

[v-cgao-msft · Accepted solution]

Hi @konradjonsson,

Try to determine if you are at the "total level" with ISINSCOPE and do the calculations separately:

Measure = IF(ISINSCOPE('financials'[Category]),[Plan (Budget) SLOW],[Plan (Budget) INCORRECT TOTALS])

ISINSCOPE – DAX Guide
The importance of star schemas in Power BI - SQLBI

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group