Measure queries all rows before on-page filters.

[EHa]

Hi All, 

I have a report where the user is viewing the results of a model, and at first filters the page with a slicer which selects the model run they want to view. This report is connecting to an sql server via direct query.

I then have several visuals showing currency values, and have another slider on the page allowing the viewer to cycle through which currency they want to view the results in, and then use the simple following measure for each displayed value to convert those values.

Value Converted =

If (

ISCROSSFILTERED(Results_ExchangeRates[Rate]) ,

VAR SelectedCurrency = SelectedValue( Results_ExchangeRates[Code] )

Var FXRatesIDRates = Summarize( Results_ExchangeRates , [FXRateSetID] , [Rate])

Var Result = IF(

Not IsBlank(SelectedCurrency) ,

if (

SelectedCurrency = "USD" ,

Sum('Table'[Value]) ,

SumX( FXRatesIDRates ,

Sum('Table'[Value]) * [Rate])

)

,Sum('Table'[Value])

)

return Result

)

What I am finding when I look in the sql queries is that this measure is generating queries to the tables for every single result  in the large storage tables, and then filtering on model run and other filters afterwards in the DAX , which is causing my queries to be way larger than they should be. This is causing issues everywhere.

Is it possible to rewrite my measure so that it is only calculating for the selected Model Run? I can imagine this reducing my queries significantly, but if anyone has other ideas that would be greatly appreciated, too.

Thanks, EHa

[daxer-almighty]

// Not sure if I've got the logic right as I can't see
// the model. Also, not sure if all the language
// constructs will work in the DQ mode.

[Base Value] = SUM( 'Table'[Value] )

[Value Converted] =
    VAR SelectedCurrency =
        // This line says: if there is only one visible Code
        // in the current context, take it. Otherwise use "USD".
        COALESCE( SELECTEDVALUE( Results_ExchangeRates[Code] ), "USD" )
    // This will return the rate if there's only one visible
    // in the current context. If none is visible or many, BLANK
    // will be returned.
    VAR SelectedRate = SELECTEDVALUE( Results_ExchangeRates[Rate] )
    VAR EffectiveRate = 
        // For code = "USD", return rate = 1, else take 
        // the rate from above.
        IF( SelectedCurrency = "USD",  1,  SelectedRate )
    VAR Result = [Base Value] * EffectiveRate
    RETURN
        Result