Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
EHa
Helper I
Helper I

Measure queries all rows before on-page filters.

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
1 REPLY 1
daxer-almighty
Solution Sage
Solution Sage

// 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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.