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],
            "AverageOutstandingAmount", 
            AVERAGE('Loan balance history'[outstanding amount])
        ),
        [AverageOutstandingAmount]
    ),
    USERELATIONSHIP(Calender[Date], 'Loan balance history'[Date])
)

 

I use this DAX statement in a Power BI measure to calculate the amount of 'outstanding amount'. I want to show it on different drill-down levels (year, quarter, month, week, day). So that is why I am taking a SUM of the AVERAGE outstanding amount per loan ID.

 

In the end I use this measure in a visual and I can zoom out to year level. But I do get an error that I surpas the query limit of 1331 MB.

 

We use a Power BI Pro license and I don't think that we can set a higher query limit. So what is left is optimizing the query. I don't know where to start, can someone maybe help me?

6 Replies

    • Vinnie's avatar
      Vinnie
      Helper I
      2 years ago

      Hi Anonymous , thanks again for your reply. This query processes about 6 million rows. I actually fixed the error I was getting on the visual.

       

      The visual holds two measures (one for the amount (euro) of the outstanding amount and one for the amount of loans that have this outstanding amount). Both were calculated in the same way (so a group by loan number, and then sumx or countx).

       

      I simplified the count by doing a distinctcount() instead of a group by (summarize()) and then countx().

       

      It works for now but I am still wondering how I am able to measure the amount of MB a query is using. Is that possible?

       

      The content is not assinged in a premium capacity.

  • Vinnie's avatar
    Vinnie
    Helper I
    2 years ago

    Hi Anonymous , thanks for you reply. It does proces a lot of rows. Millions. 

     

    We have a Pro license so I don't think that the workspace is assigned to Premium capacity? Is it still possible to optimize the query itself with some easy tricks?

  • 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