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.

[v-lgarikapat]

Hi @MKaufmann ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

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

Best Regards,
Lakshmi.

[MKaufmann]

@lbendlin @v-lgarikapat 
Thanks for reaching out and sorry for the late response. Here you go. https://drive.google.com/file/d/1odMSLnEmi9mEAyQwoczePgncd1_fpQjn/view?usp=sharing

[lbendlin]

I don't see a calendar table in your data model.  You also seem to make an assumption that there is a relationship between transaction country and currency?

[MKaufmann]

Yes, there is no calendar table, as the facts are on month grain and I don't see any reason to implement a proper date dimension.

No, I don't. The currency needs to be selected specifically by the user. If non is selected, EUR is default. 

[v-lgarikapat]

Hi @MKaufmann ,

If your filter isn’t working, it might be because the 'Currency Target'[Target Currency] column isn’t linked to the table where your data lives like the one with _ACT_GC, _ACT_PC, or _ACT_TRNS. To fix this, make sure there’s a proper relationship between those tables. Also, you can’t use a measure like [SelectedTargetCurrency] directly in a slicer or filter because it only works based on context. Instead, use the actual column 'Currency Target'[Target Currency] in your slicer. Lastly, if you use SELECTEDVALUE(..., "EUR"), it will always show "EUR" when nothing is selected or when multiple values are picked

Best Regards,

Lakshmi.

[v-lgarikapat]

Hi @MKaufmann ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

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

Best Regards,
Lakshmi.

[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