Dynamic Measures break fusion

[MKaufmann]

Hi Community,

I've a challenge with implementing dynamic measures switching between different types of currency conversions.

To be clear, the actual currency conversion is no problem and performance is great! 

What I want is the best performance for three cases:

1. Euro - Default, sum of pre calculated values to have best performance (named GC).
2. Posted Currency - Sum of posted values (named PC)
3. Selected Currency - Converting from Euro to any selected currency like USD or CHF (named TRNS for translated)

Some additional nodes:

• Working on a POC semantic model (import mode) for a customer, focused on Profit & Loss financial data.
• Fact table has 300M+ records; largest - for the POC out of scope - expected to exceed 1B records.
• Model runs on F64 capacity. Final version will be F128.
• Clean Star Schema
• Depending on the measure (ACT, PLAN, FC1...3, ACT @ PY, ACT @ PLAN ...) different exchange rates must be used.
• To simplify the example, I removed the posted currency.

Below the measure definition for Actuals, all other measures for Plan and Forecast are created the same way:

MEASURE 'Measures & Key Figures'[0_selectedCurrency] =
    ( //"USD"
    SELECTEDVALUE ( 'Currency Target'[CURRENCY_TARGET_ID], "EUR" ) )

MEASURE 'Measures & Key Figures'[0_AmountGC] =
    (
        VAR v1 =
            SELECTEDVALUE ( 'Data Version'[Data Version] )
        VAR ret =
            SUMX (
                ALL ( 'Data Version'[Data Version] ),
                CALCULATE (
                    SUM ( 'P&L Fact Details'[AMOUNT_GC] )
                        * IF ( SELECTEDVALUE ( 'Data Version'[Data Version] ) = v1, 1, 0 )
                )
            )
        RETURN
            IF ( ret <> 0, ret )
    )

MEASURE 'Measures & Key Figures'[1_ACT_GC] =
    ( CALCULATE ( [0_AmountGC], 'Data Version'[Data Version] = "ACT" ) )

MEASURE 'Measures & Key Figures'[1_ACT_TRNS] =
    (
        VAR aggFactPLbyCurr =
            ADDCOLUMNS (
                SUMMARIZE ( 'P&L Fact Details', 'Fiscal Period'[FISCPER_SID] ),
                "@amountGC", [1_ACT_GC],
                "@rateSelected",
                    CALCULATE (
                        SELECTEDVALUE ( zfactExchangeRates[RATE] ),
                        zfactExchangeRates[EXCHANGE_RATE_TYPE] = "DM",
                        zfactExchangeRates[CURRENCY_FROM] = "EUR"
                    )
            )
        VAR result =
            SUMX ( aggFactPLbyCurr, [@amountGC] * [@rateSelected] )
        RETURN
            result
    )

MEASURE 'Measures & Key Figures'[2_ACT] =
    (
        VAR _selectedCurrency = [0_selectedCurrency]
        RETURN
            SWITCH ( _selectedCurrency, "EUR", [1_ACT_GC], [1_ACT_TRNS] )
    )

Now the challenge. In the example I've four measures: ACT, FC1, FC2 & FC3.

When the table Currency Target is not filtered and the measure _selectedCurrency is returning "EUR" or I enter "USD" as a fixed value, performance is great. Nice query plan and great server timings.

As soon as Currency Target is filtered on one currency the query plan is getting way more complex, and server timings go down because of more SE queries.

What actaully happens is, that the SE Queries below are splitted in 4 queries. Data Version in one query per Data Version ACT, FC1, FC2 and FC3. And for zfactExchangeRates it's splitted into DM, Q1F, Q2F and Q3F.

SELECT
    'Data Version'[Data Version],
    SUM ( ( PFDATAID ( 'Data Version'[Data Version] ) <> 2 ) ),
    MIN ( MinMaxColumnPositionCallback ( PFDATAID ( 'Data Version'[Data Version] ) ) ),
    MAX ( MinMaxColumnPositionCallback ( PFDATAID ( 'Data Version'[Data Version] ) ) ),
    COUNT ( ),
    SUM ( ( PFDATAID ( 'Data Version'[Data Version] ) <> 2 ) ),
    MIN ( MinMaxColumnPositionCallback ( PFDATAID ( 'Data Version'[Data Version] ) ) ),
    MAX ( MinMaxColumnPositionCallback ( PFDATAID ( 'Data Version'[Data Version] ) ) ),
    COUNT ( ),
    SUM ( ( PFDATAID ( 'Data Version'[Data Version] ) <> 2 ) ),
    MIN ( MinMaxColumnPositionCallback ( PFDATAID ( 'Data Version'[Data Version] ) ) ),
    MAX ( MinMaxColumnPositionCallback ( PFDATAID ( 'Data Version'[Data Version] ) ) ),
    COUNT ( ),
    SUM ( ( PFDATAID ( 'Data Version'[Data Version] ) <> 2 ) ),
    MIN ( MinMaxColumnPositionCallback ( PFDATAID ( 'Data Version'[Data Version] ) ) ),
    MAX ( MinMaxColumnPositionCallback ( PFDATAID ( 'Data Version'[Data Version] ) ) ),
    COUNT ( )
FROM 'Data Version';

SELECT
    'Fiscal Period'[FISCPER_SID],
    'zfactExchangeRates'[EXCHANGE_RATE_TYPE],
    SUM ( ( PFDATAID ( 'zfactExchangeRates'[RATE] ) <> 2 ) ),
    MIN ( 'zfactExchangeRates'[RATE] ),
    MAX ( 'zfactExchangeRates'[RATE] ),
    COUNT ( ),
    SUM ( ( PFDATAID ( 'zfactExchangeRates'[RATE] ) <> 2 ) ),
    MIN ( 'zfactExchangeRates'[RATE] ),
    MAX ( 'zfactExchangeRates'[RATE] ),
    COUNT ( ),
    SUM ( ( PFDATAID ( 'zfactExchangeRates'[RATE] ) <> 2 ) ),
    MIN ( 'zfactExchangeRates'[RATE] ),
    MAX ( 'zfactExchangeRates'[RATE] ),
    COUNT ( ),
    SUM ( ( PFDATAID ( 'zfactExchangeRates'[RATE] ) <> 2 ) ),
    MIN ( 'zfactExchangeRates'[RATE] ),
    MAX ( 'zfactExchangeRates'[RATE] ),
    COUNT ( )
FROM 'zfactExchangeRates'
    LEFT OUTER JOIN 'Fiscal Period'
        ON 'zfactExchangeRates'[FISCPER_SID]='Fiscal Period'[FISCPER_SID]
    LEFT OUTER JOIN 'Currency Target'
        ON 'zfactExchangeRates'[CURRENCY_TO]='Currency Target'[CURRENCY_TARGET_ID]
WHERE
    'Fiscal Period'[FISCPER_SID] IN ( 2024005, 2024006, 2024007, 2024008, 2024009, 2024010, 2024011, 2024012, 2024001, 2024002..[12 total values, not all displayed] ) VAND
    'Fiscal Period'[Fiscal Year] = 2024 VAND
    'zfactExchangeRates'[EXCHANGE_RATE_TYPE] IN ( 'Q2F', 'Q3F', 'Q1F', 'DM' ) VAND
    'zfactExchangeRates'[CURRENCY_FROM] = 'EUR' VAND
    'Currency Target'[CURRENCY_TARGET_ID] = 'USD';

Just looking at the total time, it doesn't look too bad, but I've cases with more complex queries where the amount of SE queries rose from 204 to 1200, leading to a query time of 40 sec instead of 9 sec. Which is not acceptable!

What I want to achieve, keeping the functionality to switch between GC, PC and TRNS without fusion to break. I tried different approaches, to avoid selectedvalue() but except of "IF ( ISFILTERED ( 'Currency Target' ), [1_ACT_TRNS], [1_ACT_GC] )" It’s always leading to the same behavior. 

Any ideas on how to get similar functionality without breaking fusion? If something isn't clear or missing, please let me know.

I can share the daxx-Files, unfortunatelly I don't have a PBIX I can share with you. But I'm planning to create a version.

[lbendlin]

Yes, please do share a sample file.  Also - have you considered giving the new DAX User Defined Functions a try? See how they perform?

[MKaufmann]

Yes, I gave the UDFs a try, but same behavior. But maybe I'm a bit biased just looking at the same problem for hours and I can't think of a better solution.
Hopefully I find some time tomorrow to prepare a PBIX I can share with you guys.

[v-lgarikapat]

Hi @MKaufmann ,

Thanks for reaching out to the Microsoft fabric community forum.

@lbendlin 

Thanks for your prompt response

@MKaufmann 

Could you please provide sample data that fully represents the issue or question you're referring to? Kindly ensure the data is in a usable format (e.g.,PbIx Excel or CSV) rather than a screenshot, and does not contain any sensitive or unrelated information.

Looking forward to your response.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
Lakshmi