DAX SUMX: Storing a filtered table in a VAR and reference its columns later on in the expression

Question

In a measure (SUMX) I am filtering a table and storing it in a variable.

var currency = factFx[ALPHABETIC_CURRENCY_1]

var fxRates = FILTER(

    factMarketDataExchangeRates;

    factMarketDataExchangeRates[FX_CURRENCY] = currency

)

Then I need to do calcs that include further filtering fxRates

var exchangeRateOnTradeDate = CALCULATE(

    [Measure];

    FILTER( 

        fxRates;

        fxRates[CURVE_DATE] = tradeDate

    )

) 

This throws an error in SSDT Cannot find table fxRates 

Also, It appears intelligence is not working. But each of the following does work. But is this expected behavior?

Without table prefix: 

var exchangeRateOnTradeDate = CALCULATE(

    [Measure];

    FILTER( 

        fxRates;

        [CURVE_DATE] = tradeDate

    )

)

With the underlying table's prefix:

var exchangeRateOnTradeDate = CALCULATE(

    [Measure];

    FILTER( 

        fxRates;

        factMarketDataExchangeRates[CURVE_DATE] = tradeDate

    )

)

Answer 1

• You can only use the table[COLUMN] syntax for tables in your data model.

• Both of your working versions are equivalent to substituting in the definition of fxRates.

var currency = factFx[ALPHABETIC_CURRENCY_1] var exchangeRateOnTradeDate = CALCULATE ( [Measure]; FILTER ( FILTER ( factMarketDataExchangeRates; factMarketDataExchangeRates[FX_CURRENCY] = currency ); factMarketDataExchangeRates[CURVE_DATE] = tradeDate ) )

• Since [CURVE_DATE] ultimately derives from factMarketDataExchangeRates, using that table prefix is really what's happening under the hood, but you are allowed to use the other version where that table is abstracted away and doesn't clutter your code.

• The important thing to remember is that the fxRates variable isn't actually a table in this case, but rather a semantic trick to write code more legibly.