Measure Filter with Variables - Performance Tuning

[jbauer22]

The following measure Amount will set filter variables to a default unless there is external filter context.  It will then perform a switch to determine which column from the fact table to calculated.

The performance on this measure is decent, but I'd like to know if there are things we can do to make it faster?

Amount =

// Set Filter Variable Defaults

VAR varBookCode = {"B", "U"}

VAR varCP = SELECTEDVALUE('Dim Currency Perspective'[Currency Perspective], "USD Currency")

VAR varLedger = SELECTEDVALUE('Dim Ledger'[Ledger], "ACTUALS")

// Determine Load Level (Ledger, Journal or System Level records)

// Default Load Level 1 - Ledger

VAR varLevel = SWITCH(TRUE,

    ISFILTERED('Dim Workday Employee') || ISFILTERED('Dim Workday Time Block'), 3,

    ISFILTERED('Dim Journal Header') || ISFILTERED('Dim Journal Line') || ISFILTERED('Dim Journal Source') || ISFILTERED('Dim Activity') || ISFILTERED('Dim Resource Category') || ISFILTERED('Dim Analysis Type') || ISFILTERED('Dim Resource Type') || ISFILTERED('Dim Resource Sub Category') || ISFILTERED('Dim Calendar'[Date]) , 2,

    1)

RETURN

// Calculation the amount using the appropriate column and filter variables

SWITCH(TRUE,

    varCP = "Transaction Currency" && varLevel = 1, CALCULATE(SUM('Fact Ledger'[Ledger Transaction Amount - Explicit]), KEEPFILTERS('Fact Ledger'[Load Level] = varLevel), KEEPFILTERS('Fact Ledger'[Ledger] = varLedger), KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode)),

    varCP = "Base Currency" && varLevel = 1,  CALCULATE(SUM('Fact Ledger'[Ledger Base Amount - Explicit]), KEEPFILTERS('Fact Ledger'[Load Level] = varLevel), KEEPFILTERS('Fact Ledger'[Ledger] = varLedger), KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode)),

    varCP = "USD Currency" && varLevel = 1, CALCULATE(SUM('Fact Ledger'[Ledger USD Amount - Explicit]), KEEPFILTERS('Fact Ledger'[Load Level] = varLevel), KEEPFILTERS('Fact Ledger'[Ledger] = varLedger), KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode)),

    varCP = "Transaction Currency" && varLevel = 2, CALCULATE(SUM('Fact Ledger'[Journal Transaction Amount - Explicit]), KEEPFILTERS('Fact Ledger'[Load Level] = varLevel), KEEPFILTERS('Fact Ledger'[Ledger] = varLedger), KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode)),

    varCP = "Base Currency" && varLevel = 2, CALCULATE(SUM('Fact Ledger'[Journal Base Amount - Explicit]), KEEPFILTERS('Fact Ledger'[Load Level] = varLevel), KEEPFILTERS('Fact Ledger'[Ledger] = varLedger), KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode)),

    varCP = "USD Currency" && varLevel = 2, CALCULATE(SUM('Fact Ledger'[Journal USD Amount - Explicit]), KEEPFILTERS('Fact Ledger'[Load Level] = varLevel), KEEPFILTERS('Fact Ledger'[Ledger] = varLedger), KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode)),

    varCP = "Transaction Currency" && varLevel = 3, CALCULATE(SUM('Fact Ledger'[System Source Transaction Amount - Explicit]), KEEPFILTERS('Fact Ledger'[Load Level] = varLevel), KEEPFILTERS('Fact Ledger'[Ledger] = varLedger), KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode)),

    varCP = "Base Currency" && varLevel = 3, CALCULATE(SUM('Fact Ledger'[System Source Base Amount - Explicit]), KEEPFILTERS('Fact Ledger'[Load Level] = varLevel), KEEPFILTERS('Fact Ledger'[Ledger] = varLedger), KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode)),

    varCP = "USD Currency" && varLevel = 3, CALCULATE(SUM('Fact Ledger'[System Source USD Amount - Explicit]), KEEPFILTERS('Fact Ledger'[Load Level] = varLevel), KEEPFILTERS('Fact Ledger'[Ledger] = varLedger), KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode)),

    CALCULATE(SUM('Fact Ledger'[Ledger USD Amount - Explicit]), KEEPFILTERS('Fact Ledger'[Load Level] = varLevel), KEEPFILTERS('Fact Ledger'[Ledger] = varLedger), KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode))

    )

[Anonymous]

Hi @jbauer22 ,

Based on the description, reuse common calculations and use summarize function.

Then, modify the variable to the following formula:

VAR varLevel = SWITCH(
    TRUE,
    ISFILTERED('Dim Workday Employee') || ISFILTERED('Dim Workday Time Block'), 3,
    ISFILTERED('Dim Journal Header') || ISFILTERED('Dim Journal Line') || ISFILTERED('Dim Journal Source') || ISFILTERED('Dim Activity') || ISFILTERED('Dim Resource Category') || ISFILTERED('Dim Analysis Type') || ISFILTERED('Dim Resource Type') || ISFILTERED('Dim Resource Sub Category') || ISFILTERED('Dim Calendar'[Date]), 2,
    1
)

VAR FilteredFactLedger = CALCULATETABLE(
    'Fact Ledger',
    KEEPFILTERS('Fact Ledger'[Load Level] = varLevel),
    KEEPFILTERS('Fact Ledger'[Ledger] = varLedger),
    KEEPFILTERS('Fact Ledger'[Book Code] IN varBookCode)
)

// Calculation the amount using the appropriate column and filter variables
RETURN SWITCH(
    TRUE,
    varCP = "Transaction Currency" && varLevel = 1, SUMX(FilteredFactLedger, 'Fact Ledger'[Ledger Transaction Amount - Explicit]),
    varCP = "Base Currency" && varLevel = 1, SUMX(FilteredFactLedger, 'Fact Ledger'[Ledger Base Amount - Explicit]),
    varCP = "USD Currency" && varLevel = 1, SUMX(FilteredFactLedger, 'Fact Ledger'[Ledger USD Amount - Explicit]),
    varCP = "Transaction Currency" && varLevel = 2, SUMX(FilteredFactLedger, 'Fact Ledger'[Journal Transaction Amount - Explicit]),
    varCP = "Base Currency" && varLevel = 2, SUMX(FilteredFactLedger, 'Fact Ledger'[Journal Base Amount - Explicit]),
    varCP = "USD Currency" && varLevel = 2, SUMX(FilteredFactLedger, 'Fact Ledger'[Journal USD Amount - Explicit]),

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.