topic Re: Optimize DAX query in DAX Commands and Tips

Optimize DAX query

Vinnie — Tue, 10 Oct 2023 08:07:57 GMT

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?

Re: Optimize DAX query

Anonymous — Thu, 12 Oct 2023 02:13:29 GMT

Hi Vinnie,

Have many records did this query process? Did these content assigned in the premium capacity? If that's the case, you can try to increase the limit about query memory if it helps for your situation:

How to configure workloads in Power BI Premium - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

Re: Optimize DAX query

Vinnie — Thu, 12 Oct 2023 11:51:51 GMT

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?

Re: Optimize DAX query

sjoerdvn — Thu, 12 Oct 2023 15:31:57 GMT

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]) )

Re: Optimize DAX query

Vinnie — Mon, 16 Oct 2023 13:02:06 GMT

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).

Re: Optimize DAX query

Anonymous — Mon, 23 Oct 2023 09:16:26 GMT

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

Re: Optimize DAX query

Vinnie — Thu, 26 Oct 2023 07:35:34 GMT

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.