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:
- Euro - Default, sum of pre calculated values to have best performance (named GC).
- Posted Currency - Sum of posted values (named PC)
- 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.
