Forum Discussion

Vinnie's avatar
Vinnie
Helper I
2 years ago

Optimize DAX query

Hi all,   I am trying to optimize the following DAX query:   CALCULATE( SUMX( SUMMARIZECOLUMNS( 'Loan balance history'[source loan part id], "AverageOutstandi...
sjoerdvn's avatar
sjoerdvn
Solution Sage
2 years ago

does it perform better if you rewrite your measure like below?

CALCULATE(
    SUMX(
        VALUES('Loan balance history'[source loan part id]),
        CALCULATE(AVERAGE('Loan balance history'[outstanding amount]))
        )
     ),
    USERELATIONSHIP(Calender[Date], 'Loan balance history'[Date])
)
Vinnie's avatar
Vinnie
Helper I
2 years ago

I have adjusted the DAX code. The result (output) is the same. How can I see how much MB my query is using in Power BI service?

 

It sometimes works, sometimes it gives me the error. I would like to check how much MB the query is using (if it exceeds the 1331MB limit).

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI Vinnie,

    If these contents are host in the shared capacities, they were not able to configure or increase and will use the default settings.

    For the expression itself, it looks like a measure calculation with multiple aggregations. How many records your data table stored that need to be calculated? Any nested or invoke in other expressions?

    Regards,

    Xiaoxin Sheng