Dax Query Consumed Memory 1063 MB,exceeded

[jaryszek]

Hi Guys,

in power bi desktop I got en error :

Visual has exceeded available resources

I have run Performance Analyser and after running dax queries I got:

Resolve the error to see results

Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either reach out to your Analysis Services server administrator to increase the per-query memory limit or optimize the query so it consumes less memory. More details: consumed memory 1063 MB, memory limit 1024 MB.

I have used Run in Dax query view. 
This is my code:

DEFINE
	VAR __DS0FilterTable = 
		FILTER(
			KEEPFILTERS(VALUES('Dim_Date'[Date])),
			AND('Dim_Date'[Date] >= DATE(2025, 4, 1), 'Dim_Date'[Date] < DATE(2025, 5, 1))
		)

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL(
				ROLLUPGROUP(
					'Dim_EA_SavingPlans'[CommitmentAmountPerDay],
					'Dim_EA_AmortizedCosts_DiscountPlans'[PricingModel],
					'Dim_EA_AmortizedCosts_DiscountPlans'[ResourceLocationNormalized],
					'Dim_EA_AmortizedCosts_DiscountPlans'[OfferId],
					'Fct_EA_AmortizedCosts'[CostInBillingCurrency]
				), "IsGrandTotalRowTotal"
			),
			__DS0FilterTable,
			"Wastage_Amount", 'MeasureTable'[Wastage Amount],
			"Commitment_Amount_Per_Day_With_OnDemand", 'MeasureTable'[Commitment Amount Per Day With OnDemand],
			"Percent_Commitment_Day_Has_PricingModel", 'MeasureTable'[Percent Commitment Day Has PricingModel],
			"Wastage___in_Total_Has_Pricing_Model", 'MeasureTable'[Wastage % in Total Has Pricing Model],
			"Sum_of_Amortized_Cost__TREATAS_", 'MeasureTable'[Sum of Amortized Cost (TREATAS)]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			502,
			__DS0Core,
			[IsGrandTotalRowTotal],
			0,
			'Fct_EA_AmortizedCosts'[CostInBillingCurrency],
			1,
			'Dim_EA_SavingPlans'[CommitmentAmountPerDay],
			1,
			'Dim_EA_AmortizedCosts_DiscountPlans'[PricingModel],
			1,
			'Dim_EA_AmortizedCosts_DiscountPlans'[ResourceLocationNormalized],
			1,
			'Dim_EA_AmortizedCosts_DiscountPlans'[OfferId],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal] DESC,
	'Fct_EA_AmortizedCosts'[CostInBillingCurrency],
	'Dim_EA_SavingPlans'[CommitmentAmountPerDay],
	'Dim_EA_AmortizedCosts_DiscountPlans'[PricingModel],
	'Dim_EA_AmortizedCosts_DiscountPlans'[ResourceLocationNormalized],
	'Dim_EA_AmortizedCosts_DiscountPlans'[OfferId]

Can anybody advice what is wrong with my query?

Best,
Jacek

 

[jaryszek]

thank you. I can not share unfortunately data model...

[FBergamaschi]

OK then what you can do is (in another post so we keep thing clean):

include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Please include the DAX code of the measures that you will put in the visual you recreate so we can optimize

Need help uploading data? click here

Want faster answers? click here

[FBergamaschi · Accepted solution]

Hi @jaryszek 

the query code you pasted is generated by Power BI automatically and sent to the data model to fetch the data to populate the visual itself. There is nothing bad in the query itself. The problem is the resource usage that depends on the code of the measures, which is not in the code you sent

So, we have two options

1 - you share the pbix (via some cloud service and paste here the link) and indicate which is the problmeatic visual, so we can optimize your measures

2 - you provide a samples of data of all the tables, we need to rebuid the model, you need to provide he DAX code of each measure, how colums are goruped etc

The 1st one is faster

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI